unprotect sheet failure
 

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

unprotect sheet failure
 

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

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

unprotect sheet failure
 

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.

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

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

unprotect sheet failure
 

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.


Eric wrote:

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

--

Dave Peterson