Can't unprotect an excel worksheet in Workbook_BeforeSave

Question

I am using Workbook_BeforeSave to update some cells on a locked sheet in excel 2010. The subroutine works as desired when using ctrl-s to save, but will not unlock the sheet when using .Save in vba.

ThisWorkbook(code)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim MyPassword As String
    MyPassword = "password"

    ActiveWorkbook.Worksheets("Sheet1").Unprotect (MyPassword)

    ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = Now
    ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value = ThisWorkbook.BuiltinDocumentProperties("Author")

    ActiveWorkbook.Worksheets("Sheet1").Protect (MyPassword)
End Sub

Module1(Code)

Sub SaveMe()
    ActiveWorkbook.Save
End Sub

I have a button that calls SaveMe(). SaveMe() saves the document, activating Workbook_BeforeSave. The Worsheet fails to unprotect, causing an error when writing to A1.

The error states:

Run-time error '1004':
Application-defined or object-defined error

Answer 1

This worked for me, but it is not very elegant. I did not find a separate module for both unprotecting and writing worked.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    UnlockWorksheets
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A1").Value = Now
        .Range("A2").Value = ThisWorkbook.BuiltinDocumentProperties("Author")
    End With
    LockWorksheets
End Sub

Sub SaveMe()
    UnlockWorksheets
    ThisWorkbook.Save
    LockWorksheets
End Sub

Sub UnlockWorksheets()
    ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="password"
End Sub

Sub LockWorksheets()
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="password"
End Sub