Protect/Unprotect ALL and/or SPECIFIED Worksheets in Workbook
 

Hi:

I have a workbook with about 150 worksheets in it. Here's what I'
trying to accomplish:

1) Macro to password protect the Content *-and - * Objects -*and *
Scenarios of all but about 3-5 specified worksheets. (If someone ca
show me how to modify the below macro to exclude specified sheets,
think I'd be all set. I just don't know enough about Visual Basic t
know what to write to exclude worksheets.)

2) Macro to password protect the Objects -*and *- Scenarios but -*not
- the Content of the 3-5 specified worksheets excluded in 1) above. (I'
not too worried about this one as it's relatively simple to manually d
this one.)

3) Macro to unprotect -all - the worksheets.

There's obviously numerous ways to accomplish this. So far, I hav
created a macro that will password protect the Content, Objects an
Scenarios of all worksheets in my workbook except for those where th
Content -or - Objects -or - Scenarios are already protected. So, I hav
"sort of" accomplished the first macro requirement. I say "sort of
because if sheets where I do not want the Content protected are alread
"Objects and/or Scenarios" protected, the macro will ignore them - whic
is the desired result. However, if -all - the worksheets are completel
unprotected, the select few where I don't want the Content protecte
get fully protected.

If someone can tell me what I need to add to have the specific
worksheets ignored, I think I'd be in good shape. (It would also b
nice to know what I need to add to have the macro only execute o
specified worksheets. I could then create a second macro that woul
protect only the excluded sheets. I would also need to know how to onl
protect the Objects and Scenarios but -not - the Content.)

There's probably a completely different way of accomplishing what
require - so I'm open to ideas. I'm kind of a newbie to Visual Basi
and modified a macro that was given to me to get as far as I'v
gotten.

HERE IS THE MACRO I'M USING TO PROTECT ALL WORKSHEETS

Sub zzPasswordAppliedToAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to protect al
sheets.")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Sub


HERE'S THE MACRO I'M USING TO UNPROTECT MY WORKSHEETS

Sub zzPasswordRemovedFromAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to unprotect al
individual sheets.")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
wks.Unprotect Password:=myPwd
On Error GoTo 0
If wks.ProtectContents Then
MsgBox "The password you have entered is incorrect for at least one o
the worksheets. Click OK and the workheets that you have entered th
wrong password for will be unlocked. Once complete, try the macro agai
with the correct password."
End If
Next wks

End Sub

Thanks in advance for your help.

Dav

--
sns
-----------------------------------------------------------------------
snsd's Profile: http://www.excelforum.com/member.php...fo&userid=1591
View this thread: http://www.excelforum.com/showthread.php?threadid=27776

Protect/Unprotect ALL and/or SPECIFIED Worksheets in Workbook
 

Try a Select Case statement. Very flexible.

For Each wks In ThisWorkbook.Worksheets
Select Case wks.Name
Case "SheetA", "SheetB", "SheetC"
'Do nothing
Case "SheetX", "SheetY", "SheetZ"
' Do something else
Case Else
' All other sheets
On Error Resume Next
wks.Unprotect Password:=myPwd
On Error GoTo 0
If wks.ProtectContents Then
MsgBox strMessage = "The password you have entered is
incorrect for at least one of
the worksheets. Click OK and the workheets that you have entered
the
wrong password for will be unlocked. Once complete, try the
macro again
with the correct password."
End If
End Select
Next wks

You might also want to take a look at the CodeName property of Sheets. This
is a name that the user never sees and can only be set in the VBE at design
time. If you apply a naming convention to CodeNames, you might be able to
organize your sheet names into groups and avoid having to enumurate/list
them in a Select Case (or rely on the "Case Else" to do the bulk of the
work, which is not an especially good work habit)

i.e. if "wkp" prefix = sheets to be protected:
wkpSheetYada01...wkpSheetYada29, etc.
and "wkn" prefix = "normal" sheets to be left alone: wknSheetYada31,
etc .
For each wks in Worksheets
Select case left(wks.Codename,3)
Case "wkn"
' Leave me alone
Case "wkp"
' Protect Me
(etc)
--
HTH,

George Nicholson


(Please post responses to newsgroup but remove "Junk" from return address if
used)


"snsd" wrote in message
...

Hi:

I have a workbook with about 150 worksheets in it. Here's what I'm
trying to accomplish:

1) Macro to password protect the Content *-and - * Objects -*and * -
Scenarios of all but about 3-5 specified worksheets. (If someone can
show me how to modify the below macro to exclude specified sheets, I
think I'd be all set. I just don't know enough about Visual Basic to
know what to write to exclude worksheets.)

2) Macro to password protect the Objects -*and *- Scenarios but -*not *
- the Content of the 3-5 specified worksheets excluded in 1) above. (I'm
not too worried about this one as it's relatively simple to manually do
this one.)

3) Macro to unprotect -all - the worksheets.

There's obviously numerous ways to accomplish this. So far, I have
created a macro that will password protect the Content, Objects and
Scenarios of all worksheets in my workbook except for those where the
Content -or - Objects -or - Scenarios are already protected. So, I have
"sort of" accomplished the first macro requirement. I say "sort of"
because if sheets where I do not want the Content protected are already
"Objects and/or Scenarios" protected, the macro will ignore them - which
is the desired result. However, if -all - the worksheets are completely
unprotected, the select few where I don't want the Content protected
get fully protected.

If someone can tell me what I need to add to have the specific
worksheets ignored, I think I'd be in good shape. (It would also be
nice to know what I need to add to have the macro only execute on
specified worksheets. I could then create a second macro that would
protect only the excluded sheets. I would also need to know how to only
protect the Objects and Scenarios but -not - the Content.)

There's probably a completely different way of accomplishing what I
require - so I'm open to ideas. I'm kind of a newbie to Visual Basic
and modified a macro that was given to me to get as far as I've
gotten.

HERE IS THE MACRO I'M USING TO PROTECT ALL WORKSHEETS:

Sub zzPasswordAppliedToAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to protect all
sheets.")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
If wks.ProtectContents _
Or wks.ProtectDrawingObjects _
Or wks.ProtectScenarios Then
'already protected
Else
wks.Protect Password:=myPwd
End If
Next wks

End Sub


HERE'S THE MACRO I'M USING TO UNPROTECT MY WORKSHEETS:

Sub zzPasswordRemovedFromAllSheets()
Dim myPwd As String
Dim wks As Worksheet

myPwd = InputBox(prompt:="Please enter the password to unprotect all
individual sheets.")

If Trim(myPwd) = "" Then
Exit Sub
End If

For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
wks.Unprotect Password:=myPwd
On Error GoTo 0
If wks.ProtectContents Then
MsgBox "The password you have entered is incorrect for at least one of
the worksheets. Click OK and the workheets that you have entered the
wrong password for will be unlocked. Once complete, try the macro again
with the correct password."
End If
Next wks

End Sub

Thanks in advance for your help.

Dave


--
snsd
------------------------------------------------------------------------
snsd's Profile:
http://www.excelforum.com/member.php...o&userid=15910
View this thread: http://www.excelforum.com/showthread...hreadid=277765