Macro not protecting every sheet

[Brad]

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

 

[Tom Ogilvy]

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

 

[Jim Thomlinson]

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...

 

[Brad]

What should the hval be set as -- constant?

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

 

[Brad]

All of them are "regular" worksheets - no chart sheets or macro sheets.

 

[Dave Peterson]

dim hval as long

What should the hval be set as -- constant?

 

[Brad]

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??

 

[Tom Ogilvy]

Check if it is already protected before you try to protect it.