Protect/unprotect worksheets

D

davegb

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!
 
J

Jim Thomlinson

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
 
N

n.hebb

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top