protect & unprotect sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil Handal - 24 Jun 2007 12:09 GMT

Is there a way to protect and unprotect *multiple* sheets at once. All the
sheets have the same password (say 1234).

Reply to this Message

Nick Hodge - 24 Jun 2007 13:33 GMT

Khalil

You could use code like below, but cannot do it through the UI

Sub ProtectAll()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Password:="1234"
Next wks
End Sub

Sub UnProtectAll()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Unprotect Password:="1234"
Next wks
End Sub

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

> Is there a way to protect and unprotect *multiple* sheets at once. All the
> sheets have the same password (say 1234).

Reply to this Message

Gary''s Student - 24 Jun 2007 13:42 GMT

If you have sheets within a workbook that are all password protected with
"secret", then this small macro will un-protect all at once:

Sub AtRisk()
MyPass = "secret"
For Each sh In Sheets
sh.Unprotect (MyPass)
Next
End Sub

Signature

Gary''s Student - gsnu200733

> Is there a way to protect and unprotect *multiple* sheets at once. All the
> sheets have the same password (say 1234).

Reply to this Message

Khalil Handal - 24 Jun 2007 15:05 GMT

Thank you both. It worked fine

> If you have sheets within a workbook that are all password protected with
> "secret", then this small macro will un-protect all at once:
[quoted text clipped - 9 lines]
>> the
>> sheets have the same password (say 1234).

Reply to this Message

Gord Dibben - 24 Jun 2007 15:35 GMT

See response at the other thread you tagged onto.

Gord Dibben MS Excel MVP

>Is there a way to protect and unprotect *multiple* sheets at once. All the
>sheets have the same password (say 1234).

Reply to this Message

Khalil Handal - 24 Jun 2007 20:59 GMT

Thanks for the note. Withot it I would have missed it.

> See response at the other thread you tagged onto.
>
> Gord Dibben MS Excel MVP
>
>>Is there a way to protect and unprotect *multiple* sheets at once. All the
>>sheets have the same password (say 1234).

Reply to this Message

Rate this thread: