S
snsd
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
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