Protecting & Unprotecting multiple sheets.

Moradyn

New Member
Joined
Apr 12, 2002
Messages
17
Anyone have a link to some code that protects and unprotects multiple sheets in one go ?
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

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.
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

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.
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

Thank you so much for your assistance! This worked like a charm!!:biggrin:
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

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
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

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

Code:
Sub UnProtectWS()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Sheet1","Sheet3"))
    For Each ws In WSArray
         ws.Unprotect Password:="password"
    Next
End Sub
 
Upvote 0
Re: Protecting & Unprotecting multiple sheets.

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?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top