CODEWITHSUNDEEP
excelevent-handlingvba
Sjoerd
Sjoerd

Reputation:

Trap the Worksheet Unprotect event in Excel

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?

Upvotes: 3

Views: 4517

Answers (2)

Fionnuala
Fionnuala

Reputation: 91316

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

Upvotes: 2

dbb
dbb

Reputation: 2877

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

Upvotes: 0

Related Questions