Mass Protect and unProtect

[RobN]

Is there some code that will protect and some more code that will Unprotect
all spreadsheets in a workbook?

I thought something like this would work if I named all the sheets, but it
doesn't!
Copied from Help and change Select to
Protect.........Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Protect
What's wrong with this code? ("Says subscript out of range") All those
sheets exist. In fact this won't even work on 1 sheet!

However, I'd rather some code that did all the sheets, even if I added
others.

Rob

 

[Guest]

Hi Rob,

The following works but depending on just what you want to protect, you
might want to set some more parameters for the protect.

Sub Test_Protect()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Protect Password:="ossiemac"
Next ws

End Sub

Sub Test_Un_Protect()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Unprotect Password:="ossiemac"
Next ws

End Sub

 

[RobN]

Thanks OssieMac, just what I needed!!!

Just a matter of interest, any idea why the UnProtect runs MUCH slower? The
sheets are mainly filled with charts, Pivot Tables and Pivot Charts & Data.
Not too many formulas. There are 23 sheets and takes 5 seconds to UnProtect
and about 1/2 sec to Protect.

Rob

 

[Guest]

Hi again Rob,

Other than being facetious and saying that one can run faster going forwards
rather than backwards, I really don’t know the answer. I think that it would
be necessary to have access to the source code and find out what Excel and
the macros have to process behind the scenes to answer the question.

However, I am pleased that I was able to help you with the original question.

 

[RobN]

Hi OssieMac,

It was just an interesting observation I made, particularly as there is no
other code! I'm just running your code in a couple of modules as stand
alones.

Anyway, no need to reply, but thanks.

Rob