Macro not protecting every sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is the macro used and it did not password protect every sheet in the
workbook. At the time that the macro was ran there were some sheets that we
hidded - some were very hidden. But that shouldn't make a difference should
it?

I have about 30 sheets in the workbook.

Sub lockdown()
Const pword As String = "XXXX"
Dim sht As Worksheet
For Each sht In Worksheets
sht.Protect pword
Next sht
End Sub
 
I don't know - but see if this does better:
Sub lockdown()
Const pword As String = "XXXX"
Dim sht As Worksheet, sh as Worksheet
set sh = Activesheet
Application.ScreenUpdating = False
For Each sht In Worksheets
hval = sht.Visible
sht.visible = xlSheetVisible
sht.Select
sht.Protect pword
sht.Visible = hval
Next sht
sh.Select
Application.ScreenUpdating = True
End Sub
 
Out on a limb here but are they all worksheets or are some of them chart
sheets or macro sheets... If so then you want to go through the Sheets
collection and not the worksheets collection...
 
What should the hval be set as -- constant?

Tom Ogilvy said:
I don't know - but see if this does better:
Sub lockdown()
Const pword As String = "XXXX"
Dim sht As Worksheet, sh as Worksheet
set sh = Activesheet
Application.ScreenUpdating = False
For Each sht In Worksheets
hval = sht.Visible
sht.visible = xlSheetVisible
sht.Select
sht.Protect pword
sht.Visible = hval
Next sht
sh.Select
Application.ScreenUpdating = True
End Sub
Click to expand...
 
Figured out what the problem was - the sheets were already protected but not
password protected. When I unprotected the sheets, the original macro worked
fine.

What should I have done differently to make the macro work whether the sheet
was protected or not??
 
Back
Top