Trap the Worksheet Unprotect event in Excel

Question

I have a protected Excel worksheet, without a password. What I'd like to do is trap the event that a user unprotects the worksheet, so that I can generate a message (and nag 'em!). I can setup event checking for the application, for when new workbooks are opened, etc., but not for Unprotect.
Does anyone have an idea?

Answer 1

It is possible to modify the menu using Tools->Customize. Protect/Unprotect can be set to run a macro, for example:

Sub UnprotectTrap()
If ActiveSheet.ProtectContents = True Then
    MsgBox "Tut,tut!"
    ActiveSheet.Unprotect
Else
    ActiveSheet.Protect

End If
End Sub

Answer 2

There is no way to trap the user unprotecting the sheet, but you can warn them if they save the workbook without reprotecting the sheet(s).

In the Workbook module, put this code, or something like it

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If Sheets("MyProtectedSheet").ProtectContents = False Then
    MsgBox "The sheet 'MyProtectedSheet' should not be left unprotected. I will protect it before saving", vbInformation
    Sheets("MyProtectedSheet").Protect
  End If
End Sub

Answer 3

Should have thought of that - thanks.