unprotect sheet failure

[Eric]

I'm using the code below to switch between a development enviroment, where I
can view private data and change anything I want to on a sheet, and a user
environment where private data is hidden and the user interface is
protected.

It works, EXCEPT for whatever happens to be the active sheet when I invoke
the macro.

Thanks In Advance!
Eric

Public Sub ProtectAllSheets(protectionLevel As ProtectionOptions)
On Error GoTo ProtectAllSheets_Error

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
Select Case protectionLevel
Case Protect
Call wks.Protect(UserInterfaceOnly:=False, Password:=pWord)
Case Unprotect
Call wks.Unprotect(pWord)
End Select
Next wks

On Error GoTo 0
Exit Sub

ProtectAllSheets_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
ProtectAllSheets of Module MSecurity"

End Sub

 

[Gleam]

Have you tried quotation marks around the select parameters?
Case "Protect"
Call wks.Protect(UserInterfaceOnly:=False,
Password:=Pword)Case "Unprotect"
HTH

 

[Eric]

No, those case breaks are just normal enums. And the routine does work for
every sheet in the workbook except for the active sheet.

Thanks anyway.

 

[Dave Peterson]

Maybe the password isn't the same for the activesheet???

I don't think it should matter, but that's kind of a weird use of the Call
statement. I'd use:

For Each wks In ActiveWorkbook.Sheets
Select Case protectionLevel
Case Protect
wks.Protect UserInterfaceOnly:=False, Password:=pWord
Case Unprotect
wks.Unprotect password:=pWord 'I like keywords!
End Select
Next wks


And I'm not sure I'd use Protect and Unprotect as constants/enums either. They
may not confuse excel, but they would me.