Protect-Unprotect all the sheets
 

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

Protect-Unprotect all the sheets
 

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

On Feb 23, 8:21 pm, "Gary" wrote:
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

Protect-Unprotect all the sheets
 

Tools Protection Protect Workbook

"Gary" wrote:

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

Protect-Unprotect all the sheets
 

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" wrote:

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

Protect-Unprotect all the sheets
 

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


"Pete_UK" wrote in message
ups.com...
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

On Feb 23, 8:21 pm, "Gary" wrote:
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

Protect-Unprotect all the sheets
 

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

On Feb 23, 8:35 pm, "Pete_UK" wrote:
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

On Feb 23, 8:21 pm, "Gary" wrote:



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- Hide quoted text -

- Show quoted text -

Protect-Unprotect all the sheets
 

Hi Mike,

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

"Mike" wrote in message
...
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" wrote:

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

Protect-Unprotect all the sheets
 

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

On Fri, 23 Feb 2007 16:52:39 -0700, "Gary" wrote:

Hi Mike,

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

"Mike" wrote in message
...
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" wrote:

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