Locking and Protecting All Sheets in a Workbook

[Jack Gillis]

I am trying to write a simple macro to lock all cells in all worksheets
for the Active Workbook. The number of sheets in a Workbook can vary
from one to an undetermined number.

Here is what I thought would work but it doesn't. It creates an error
on the Selections.Locked statement if a sheet is protected when entering
the Sub. If all sheets are unprotected, of course it works fine but
that is seldom the case.

Can someone help me out with this problem? I've searched the group but
can't seem to find the answer.

Thank you very much.


Sub ProtectAndLockAllSheets()
Dim WkSht As Worksheet

For Each WkSht In ActiveWorkbook.Worksheets

WkSht.Unprotect 'This is the hang up and needs to be
re-worked to unprotect the protected sheet!
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False

Next


For Each WkSht In ActiveWorkbook.Worksheets
WkSht.Protect
Next

End Sub

 

[JE McGimpsey]

Cells, when used without qualification, refers to the ActiveSheet. But
you don't have to select at all:


Public Sub ProtectAndLockAllSheets()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
.Unprotect
.Cells.Locked = True
.Cells.FormulaHidden = False
.Protect
End With
Next wkSht
End Sub