Hi, I have some code that I run on a lot of my workbooks which protects
all the worksheets with my password "PASS" the only thing is, is
that some of the properties of the protect function are not kept when
it is run. Specifically I want to have the sheet protected and only
want users to be able to 'Select unlocked cells' but it seems when
the code is run it enables users to 'Select locked cells' can
anyone help??

Function ProtectAll()

Application.ScreenUpdating = False
Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Visible = xlSheetVisible
Next

For Each Sh In Worksheets
Sh.Protect "PASS"
Next

For Each Sh In Worksheets
Sh.Select
With ActiveWindow
.DisplayHeadings = False
End With
Next

For Each Sh In Worksheets
Sh.Visible = xlHidden
Sheets("Cover").Visible = xlSheetVisible
Sheets("Cover").Select
Next

ActiveWorkbook.Protect "PASS"
Application.ScreenUpdating = True

End Function

Regards,

Scott Spence

ah ha!

thanks Chris 'Sh.EnableSelection = xlUnlockedCells '
was what i was after

thank you very much.

eAlchemist wrote:
I condensed all of the for next loops- not necessary to do all of it
separately. This should probably run as a sub, rather than a function,
unless you're returning some value.

Sub ProtectAll()

Application.ScreenUpdating = False
Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Visible = xlSheetVisible
Sh.Protect "PASS"
Sh.EnableSelection = xlUnlockedCells
Sh.Select
ActiveWindow.DisplayHeadings = False
Sh.Visible = xlHidden
Next

Sheets("Cover").Visible = xlSheetVisible
Sheets("Cover").Select

ActiveWorkbook.Protect "PASS"
Application.ScreenUpdating = True
End Sub

--
Chris Farkas
Excel/Access Developer
www.eAlchemy.biz


"spences10" wrote:

Hi, I have some code that I run on a lot of my workbooks which protects
all the worksheets with my password "PASS" the only thing is, is
that some of the properties of the protect function are not kept when
it is run. Specifically I want to have the sheet protected and only
want users to be able to 'Select unlocked cells' but it seems when
the code is run it enables users to 'Select locked cells' can
anyone help??

Function ProtectAll()

Application.ScreenUpdating = False
Dim Sh As Worksheet

For Each Sh In Worksheets
Sh.Visible = xlSheetVisible
Next

For Each Sh In Worksheets
Sh.Protect "PASS"
Next

For Each Sh In Worksheets
Sh.Select
With ActiveWindow
.DisplayHeadings = False
End With
Next

For Each Sh In Worksheets
Sh.Visible = xlHidden
Sheets("Cover").Visible = xlSheetVisible
Sheets("Cover").Select
Next

ActiveWorkbook.Protect "PASS"
Application.ScreenUpdating = True

End Function

Regards,

Scott Spence