Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect/unprotect worksheets
I've been working on this macro to protect and unprotect the worksheets
in a workbook I'm sending out to novice end users. Problem is, sometimes it works, and sometimes it doesn't. Sub AllSheetsToggleProtectWGrid() 'for all sheets in currently active workbook, assigned to button 'Password Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet Dim PWORD As String Dim WksWasProtected As Boolean PWORD = "dave" Application.ScreenUpdating = False For Each wkSht In ActiveWorkbook.Worksheets If LCase(wkSht.Name) = LCase("County Records") Then 'do nothing Else With wkSht wkSht.Select<-----VBA Error "Method Select of object worksheet failed If .ProtectContents Then WksWasProtected = True .Unprotect Password:=PWORD ActiveWindow.DisplayGridlines = True Else wkSht.Select WksWasProtected = False ActiveWindow.DisplayGridlines = False .Protect Password:=PWORD If WksWasProtected Then .Protect Password:=PWORD End If 'End If End If End With End If Next wkSht Application.ScreenUpdating = True End Sub It's failing at the wkSht.select command, which was working fine for a while. I made some changes to some of the details of the formatting in the target sheet, "County Records", that I didn't think would affect this. Not sure what's going on. Does anyone have any ideas? For clarification, the worksheet "County Records" does not get protected, but all the other sheets do. Thanks for the help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect/unprotect worksheets
Just a hunch... Select will not work if there are hidden work sheets. I did
not look at all of your code but that is a good place to start. HTH "davegb" wrote: I've been working on this macro to protect and unprotect the worksheets in a workbook I'm sending out to novice end users. Problem is, sometimes it works, and sometimes it doesn't. Sub AllSheetsToggleProtectWGrid() 'for all sheets in currently active workbook, assigned to button 'Password Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet Dim PWORD As String Dim WksWasProtected As Boolean PWORD = "dave" Application.ScreenUpdating = False For Each wkSht In ActiveWorkbook.Worksheets If LCase(wkSht.Name) = LCase("County Records") Then 'do nothing Else With wkSht wkSht.Select<-----VBA Error "Method Select of object worksheet failed If .ProtectContents Then WksWasProtected = True .Unprotect Password:=PWORD ActiveWindow.DisplayGridlines = True Else wkSht.Select WksWasProtected = False ActiveWindow.DisplayGridlines = False .Protect Password:=PWORD If WksWasProtected Then .Protect Password:=PWORD End If 'End If End If End With End If Next wkSht Application.ScreenUpdating = True End Sub It's failing at the wkSht.select command, which was working fine for a while. I made some changes to some of the details of the formatting in the target sheet, "County Records", that I didn't think would affect this. Not sure what's going on. Does anyone have any ideas? For clarification, the worksheet "County Records" does not get protected, but all the other sheets do. Thanks for the help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect/unprotect worksheets
I tried it out.
Change: If LCase(wkSht.Name) = LCase("County Records") Then 'do nothing Else To: If Not LCase(wkSht.Name) = "county records" And wkSht.Visible Then Alternately, if you want to toggle the grids on the hidden worksheets as well, you can set the wkSht.Visble property to True before the changes and revert it back afterwards. -- Nick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect and Unprotect all worksheets with macro | Excel Discussion (Misc queries) | |||
protect/unprotect grouped worksheets | Excel Programming | |||
Protect/unprotect all worksheets | Excel Worksheet Functions | |||
protect / unprotect worksheets using VBA | Excel Programming | |||
Macro to protect/unprotect worksheets | Excel Programming |