Can't unprotect an excel worksheet in Workbook_BeforeSave

0
votes

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
2
excelvba
Why not use ThisWorkbook inplace of ActiveWorkbook ? If you're operating on the workbook which contains the code, then ThisWorkbook is safer... – Tim Williams
I've tried both. In my actual project I am using Thisworkbook. – Dave Thunes
"Application-defined or object-defined error" is what you get when a VBA error occurs, but you don't have any error-handling. Add an error-handler to the failing routine and have it display a MsgBox with the actual VBA error number and description. – RBarryYoung
RBarryYoung - I don't understand what you mean. I tried: MsgBox Err & ": " & Error(Err). That gave me the same error number and message – Dave Thunes

2 Answers

1
votes

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
0
votes

why not put the 'before save' module into a new sub routine and have the before close call that

EG.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Call SomeSub
End Sub

sub someSub()
'Code here
end sub

sub Button()
call SomeCub
activeworkbook.save
end sub