Protect multiple sheets in one time
 

I am using work books containing 50+ sheets and all of the sheets have to be
protected to prevent users from changing or damaging formula's in locked
cells. I did not find a method yet to protect more than 1 sheet at the same
time (using the same password). Does anyone of you know a way to handle this?

Many thanks in advance,

Protect multiple sheets in one time
 

Good morning Henk Frigge

This code will do what you're asking.

Sub ProtSheets()
For Each WkSht In Worksheets
WkSht.Protect password:="password"
Next WkSht
End Sub

Note that for simplicity I have omitted some of the optional settings
that can be used in th third "Protect" line. (In XL2000
(DrawingObjects:=True, Contents:=True, Scenarios:=True but in XL
2002/2003 around a dozen other optional settings)

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=383063

Protect multiple sheets in one time
 

Henk, here is one way,

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect password:="123"
Next ws
End Sub


And to unprotect

Sub unprotect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Henk Frigge" <Henk wrote in message
...
I am using work books containing 50+ sheets and all of the sheets have to
be
protected to prevent users from changing or damaging formula's in locked
cells. I did not find a method yet to protect more than 1 sheet at the
same
time (using the same password). Does anyone of you know a way to handle
this?

Many thanks in advance,

Protect multiple sheets in one time
 

tHENKs Paul!

Working great!

"Paul B" wrote:

Henk, here is one way,

Sub protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect password:="123"
Next ws
End Sub


And to unprotect

Sub unprotect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:="123"
Next ws
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Henk Frigge" <Henk wrote in message
...
I am using work books containing 50+ sheets and all of the sheets have to
be
protected to prevent users from changing or damaging formula's in locked
cells. I did not find a method yet to protect more than 1 sheet at the
same
time (using the same password). Does anyone of you know a way to handle
this?

Many thanks in advance,

Protect multiple sheets in one time
 

tHENKs Dominic

This one is working perfectly too!

"dominicb" wrote:


Good morning Henk Frigge

This code will do what you're asking.

Sub ProtSheets()
For Each WkSht In Worksheets
WkSht.Protect password:="password"
Next WkSht
End Sub

Note that for simplicity I have omitted some of the optional settings
that can be used in th third "Protect" line. (In XL2000
(DrawingObjects:=True, Contents:=True, Scenarios:=True but in XL
2002/2003 around a dozen other optional settings)

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=383063