Protecting & Unprotecting multiple sheets.

[Moradyn]

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

 

[lapta301]

Re: Protecting & Unprotecting multiple sheets.

Dear All

Not sure who will see this but I have used the following code on a test workbook which works fine:

Sub ProtectWS()
Dim ws As Worksheet
Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
For Each ws In wsArray
ws.Protect Password:="fred"
'ws.Range("A1").Select
Next
End Sub

Sub UnProtectWS()
Dim ws As Worksheet
Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
For Each ws In wsArray
ws.Unprotect Password:="fred"
Next
End Sub

However in a much larger project the only way it would work is to define Dim wsArray as a Variant.

We have checked the code and there are no other arrays. Is there something else that we should be looking for?

Any advice would be appreciated

 

[Jasuan]

Re: Protecting & Unprotecting multiple sheets.

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 -

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

Shades,

First of all, this code is awesome! Thank you. Before I added this code if I wanted to individually unprotect a sheet, I didn't need a password. Now that I added this code however, a password is now required to unlock individual pages (which automatically became "whatever"). Is there a variant of this code that would not require a password to unlock each page individually nor by using the macros?

 

[Jasuan]

Re: Protecting & Unprotecting multiple sheets.

Shades,

First of all, this code is awesome! Thank you. Before I added this code if I wanted to individually unprotect a sheet, I didn't need a password. Now that I added this code however, a password is now required to unlock individual pages (which automatically became "whatever"). Is there a variant of this code that would not require a password to unlock each page individually nor by using the macros?

I figured this out, but can't figure out how to delete my Reply.

When I protect the worksheets, I need to be able to select a few options of the "Allow all users of this worksheet to:" box.

The options I need to have checked are: "Edit Objects," "Format Cells" and "Select Unlocked Cells."

Thank you all in advance.

 

[merlin777]

Re: Protecting & Unprotecting multiple sheets.

I'm using these two macros. My array has 20 sheets in it. The only change I made, apart from the array contents, is to remove 'password="fred"' because I don't have a password on the protected sheets.

unprotectws works fine but when I run protectws I get the error 'runtime error 1004 method 'protect' of object '_worksheet' failed'

Can anyone spot the problem, please?

Dear All

Not sure who will see this but I have used the following code on a test workbook which works fine:

Sub ProtectWS()
Dim ws As Worksheet
Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
For Each ws In wsArray
ws.Protect Password:="fred"
'ws.Range("A1").Select
Next
End Sub

Sub UnProtectWS()
Dim ws As Worksheet
Set wsArray = Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"))
For Each ws In wsArray
ws.Unprotect Password:="fred"
Next
End Sub

However in a much larger project the only way it would work is to define Dim wsArray as a Variant.

We have checked the code and there are no other arrays. Is there something else that we should be looking for?

Any advice would be appreciated

 

[Jon von der Heyden]

Re: Protecting & Unprotecting multiple sheets.

Have you tried to debug which sheet in the array it is breaking on?

 

[merlin777]

Re: Protecting & Unprotecting multiple sheets.

It breaks on the first pass. I just managed to find the problem.

both macros are fine but the problem was what i was doing in between. I was unprotecting, grouping the sheets to perform a common change then reprotecting.

The code in between left the sheets grouped and that caused the error.

thanks for your help.