Protect and Unprotect all worksheets with macro

[Joe M.]

I would like to protect and also unprotect all worksheets in the same
workbook with a macro. I do not need to password protect as this is only for
my own use.

Also I would like to be able to protect / unprotect worksheets based upon
the value of a cell within each worksheet.

Thanks,
Joe M.

 

[Gord Dibben]

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect 'Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Unprotect 'Password:="justme"
Next N
Application.ScreenUpdating = True
End Sub

Second part can be done but need some details.

Any sheet? All sheets? Which cell? How is the value entered?

Calculated value or manually entered value?


Gord Dibben MS Excel MVP

 

[Mike H]

Joe,

Try this macro but be aware that when it is protected you won't be able to
change the cell value to unprotect unless you manually unprotect first so the
unprotect bit is somewhat superfluous

Sub Protect()
For x = 1 To Worksheets.Count
If UCase(Sheets(x).Range("A1")) = "P" Then
Sheets(x).Protect
Else
Sheets(x).Unprotect
End If
Next
End Sub


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.