Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup failure & ctrl-f failure? | Excel Discussion (Misc queries) | |||
Ctrl+Home failure when sheet protected | Excel Discussion (Misc queries) | |||
Copying objects to new sheet failure | Excel Discussion (Misc queries) | |||
Chart versus Work Sheet Code Failure | Excel Programming | |||
unprotect sheet in code and make sheet visible | Excel Programming |