Protecting & Unprotecting multiple sheets.

[Moradyn]

Anyone have a link to some code that protects and unprotects multiple sheets in one go ?

[Al Chara]

You cannot protect/unprotect more than one sheet at once, have you tried the following:

Worksheets("Sheet1").Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Sheet1").Unprotect ("password")
Worksheets("Sheet2").Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Worksheets("Sheet2").Unprotect ("password")

Edit as needed.

[shades]

Actually, someone gave me the following a few weeks ago. These two macros will do it for all sheets in a workbook.


Sub ProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Protect password:="whatever", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next ws

End Sub


-----------
Sub DeProtectAll()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect password:="whatever"
Next ws

End Sub

NOTE:
"whatever" is the password (without the quotes) you supply
_________________
- old, slow, and confused
... but at least I'm inconsistent -

[ This Message was edited by: shades on 2002-04-15 08:02 ]

[Al Chara]

Shades code will work if you want to do all of the sheets of a workbook, but not if you only want to do some of the worksheets. If you want to use a for loop, then you will have to do something like the following:

Sub ProtectWS()
Dim ws As Worksheet
Set WSArray = Sheets(Array("Sheet1", "Sheet3"))
For Each ws In WSArray
ws.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
end sub

Edit as needed.

[Agieseke]

I am new to VBA code, so I don't fully understand. If I insert this into my VBA code, what sheet do I use - This Workbook or Module? Also, once I have it inserted, how do I run it? Sorry for the basic questions, but until I get training (sometime in September) I have to go this route.

[Jon von der Heyden]

Hi and welcome to the board!

The code you refer to should sit within a standard module. You can either run it by hitting ALT+F8 and then choosing the macro from the dialog that opens, or you can attach the macro to a forms button in one of your sheets.

[Agieseke]

Thank you so much for your assistance! This worked like a charm!!

[Agieseke]

I'm sorry, one last question. Can I modify this code to unprotect the worksheets as well? I tried the following:
Sub UnProtectWS()
Dim ws As Worksheet
Set WSArray = Sheets(Array("Sheet1","Sheet3"))
For Each ws In WSArray
ws.Unprotect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

However, I get a Compile Error:Named argument not found, and the "DrawingObjects:" is highlighted. What am I doing wrong?

Thank you ~ Agieseke

[Jon von der Heyden]

Those arguments are not available when you unprotect, only when you protect. So it would be:

Sub UnProtectWS()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Sheet1","Sheet3"))
    For Each ws In WSArray
         ws.Unprotect Password:="password"
    Next
End Sub

[jlouwagie]

I am using the above macros and have been successful in protecting multiple worksheets with a password, but I can't seem to protect them in a way that allows me to continue to use the Pivot Table Reports. Is there additional language that I can include that will select only "Use Pivot Table Reports" from the list on the "Protect Sheet" screen?