Unprotecting multiple worksheets w/ Macro

T

tippy

I am protecting with a macro, multiple worksheets in a workbook with a
macro. I can't get the unprotect of multiple worksheets to work.
Does anyone have a working macro to do this.


_____________

Tippy
 
T

Tom Ogilvy

Sub UnprotectSheets()
Dim sh as Worksheets
for each sh in ThisWorkbook.Worksheets
if sh.ProtectContents or sh.ProtectScenarios or _
sh.ProtectDrawingObjects then
sh.unprotect Password:="ABCD"
end if
Next
End Sub
 
J

J.E. McGimpsey

one way:

Public Sub UnProtectMultipleSheets()
Const cPword As String = "drowssap"
Dim oWkSht As Worksheet
For Each oWkSht In Worksheets
oWkSht.Unprotect cPword
Next oWkSht
End Sub
 
T

tippy

one way:

Public Sub UnProtectMultipleSheets()
Const cPword As String = "drowssap"
Dim oWkSht As Worksheet
For Each oWkSht In Worksheets
oWkSht.Unprotect cPword
Next oWkSht
End Sub
Click to expand...


I was using something very similar trying to use something you had
posted on protecting multiple spreadsheets.

It seems the lower case "c" in fron of Pword" and the "o" in fron of
Wksht are the major differences. Also I think I had PWord = Password.
I'll give it a shot. Thanks.


_____________

Tippy
 
T

Tom Ogilvy

Public Sub UnProtectMultipleSheets()
Const Pword As String = "drowssap"
Dim WkSht As Worksheet
For Each WkSht In Worksheets
WkSht.Unprotect Pword
Next WkSht
End Sub

Would work just as well. Yes you would need to supply the actual password.
 
J

J.E. McGimpsey

The c and the o are only mnemonics (for constant and object) that
were specified for variables in the code I lifted the procedure from
and have no effect on the operation of the macro. This would work
just as well:

Public Sub Larry()
Const Curly As String = "drowssap"
Dim Moe As Worksheet
For Each Moe In Worksheets
Moe.Unprotect Password:=Curly
Next Moe
End Sub
 
T

tippy

The macro below still gives me an error.

Sub UnProtectMultipleSheets()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet
For Each wkSht In Worksheets
wkSht.Unprotect PWORD
Next wkSht
End Sub



Although I protect the test worksheets with "drowssap" using a macro,
the unprotect above does not work. I get run time error 1004 and the
message that the password is not correct. I checked the spelling and
case, no luck.

Here is the macro, which works well, that I used to protect the
worksheets.

Public Sub ProtectAll()
Const PWORD As String = "drowssap"
Dim wkSht As Worksheet
For Each wkSht In Worksheets
wkSht.Protect PASSWORD:=PWORD
Next wkSht
End Sub














The c and the o are only mnemonics (for constant and object) that
were specified for variables in the code I lifted the procedure from
and have no effect on the operation of the macro. This would work
just as well:

Public Sub Larry()
Const Curly As String = "drowssap"
Dim Moe As Worksheet
For Each Moe In Worksheets
Moe.Unprotect Password:=Curly
Next Moe
End Sub
Click to expand...

_____________

Tippy
 
J

J.E. McGimpsey

I just pasted both routines into a workbook and they both work fine.
The only way I can get the Unprotect to fail is if the protected
book is not active.

Hmm...
 
T

tippy

I don't understand. It is "active" when the first macro works, stored
in module 1. The 2nd module contains the unprotect macro. If the
first macro works during a session, why wouldn't the 2nd? I use
shortcut keys Ctrl-p and Ctrl-u, which should have no bearing.

Hmmm -- I noticed your specific reference to "protected book," and I'm
referring to protected "worksheets" in a single workbook, but I
suspect you mean the same thing.

Maybe there is something more basic that I haven't considered....

I just pasted both routines into a workbook and they both work fine.
The only way I can get the Unprotect to fail is if the protected
book is not active.

Hmm...
Click to expand...

_____________

Tippy
 
J

J.E. McGimpsey

Is it possible that you've protected one or more sheets with a
different password before you run the ProtectAll macro?

Running ProtectAll on a protected sheet won't change it's original
password, and will give you the run-time error when you try to
unprotect, since you're using the wrong password.
 
T

tippy

Nope, all protected with the same macro. I am stumped.


Is it possible that you've protected one or more sheets with a
different password before you run the ProtectAll macro?

Running ProtectAll on a protected sheet won't change it's original
password, and will give you the run-time error when you try to
unprotect, since you're using the wrong password.
Click to expand...

_____________

Tippy
 
Top