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!

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!

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