Protect-Unprotect all the sheets

[Gary]

Hi All,

If i have a workbook with 15 or more sheets and i want to protect /
unprotect all of them together. How do I do it? going to every sheet and
doing it takes a lot of time....am sure there must be a way of doing that.

Thanks
gary

 

[Pete_UK]

You could group the sheets together - right-click on the sheet tab and
click on Select All Sheets. Then whatever you do to one sheet will
also be done to the other sheets, so be careful - select Tools |
Protection | Protect sheet and give your password. You must remember
to right-click the sheet tab again to Ungroup Sheets.

Alternatively, you could have a VBA macro to protect all sheets and
another one to unprotect them.

Hope this helps.

Pete

 

[Guest]

Tools > Protection > Protect Workbook

 

[Guest]

provided all sheets have the same password, this will do

Sub dontblameme()
Dim sht As Worksheet
For Each sht In Sheets
Name = sht.Name
Worksheets(Name).Select
ActiveSheet.Unprotect Password:="YOURPASSWORD"
Next sht
End Sub

 

[Gary]

hey pete, when I select all sheets, the 'Unprotect Sheet' option is
disabled. why is that?

 

[Pete_UK]

Sorry, I've just tried this and the Protect Worksheet option is greyed
out when more than one sheet is grouped, so you won't be able to do it
this way.

Pete

 

[Gary]

Hi Mike,

i copied and pasted this but it's not working.

 

[Gord Dibben]

Gary

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 Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each WS In MySheets
WS.Select
WS.Protect Password:="justme"
Next WS
End Sub

In both Subs change WS.Protect to WS.Unprotect to go the other way.


Gord Dibben MS Excel MVP