Protect Chart Sheet

[Guest]

I have a macro that protects all the worksheets in a workbook, but not the
chart sheets. I need for it to protect them as well. Can anyone help? Here is
the code I use to protect the worksheets and workbook:

Application.ScreenUpdating = False

Dim password As String

password = Application.InputBox(prompt:="What Password Do You Want To Use?",
Type:=2)
If password = "" Then
'do nothing
Else
For Each ws In Worksheets
ws.Activate
ActiveSheet.Protect (password)
Next ws
End If

Application.ThisWorkbook.Protect (password), structure:=True
Worksheets("Global & Monthly Inputs").Activate
Range("A1").Select
Application.ScreenUpdating = True

Thanks!

 

[Guest]

Try turning on the macro recorder and perform the action manually.

When I do that, I get:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

so you could do

dim ws as Object

for each ws in Sheets
ws.Activate
Activesheet.protect password
Next

 

[Guest]

I have three different workbooks that I use this code on, all having
different sheet names so the first option is out.
I tried the Dim ws as Object, and that did not fix the problem either. It
looks like there is a different Protect form that comes up with Chart Sheets.
Somehow, there must be a way to address a ChartSheet like WS is for
Worksheet. Still stumped!

David

 

[Dave Peterson]

Did you notice that Tom used this:

for each ws in Sheets

You may want to post your current code if you have trouble.

 

[Guest]

Thanks to each of you. I did not change my WorkSheets to Sheets. This was the
problem. Thanks again!

 

[Guest]

Well geez....that's weird...now one of the worksheets will not unprotect when
I use sheets instead of worksheets. Runtime error 1004, password is wrong,
but it is right and works manually. I change the code back to worksheets and
it works fine. The worksheet is graphs and lookup tables for specific
accounts and time periods.
Maybe I could do one run through all the worksheets and then another for
sheets(charts). Ever seen anything like this?
Here is all my code:

Sub ProtectAllSheets()
'
' ProtectAllSheets Macro
' Macro recorded 12/11/2005 by David L Perkins
'
' Keyboard Shortcut: Ctrl+p
'
Application.ScreenUpdating = False

Dim password As String
Dim ws As Object

password = Application.InputBox(prompt:="What Password Do You Want To Use?",
Type:=2)
If password = "" Then
'do nothing
Else
For Each ws In Sheets
ws.Activate
ActiveSheet.Protect (password)
Next ws
End If

Application.ThisWorkbook.Protect (password), structure:=True
Worksheets("Global & Monthly Inputs").Activate
Range("A1").Select
Application.ScreenUpdating = True

End Sub

Sub UnprotectAllSheets()
'
' UnprotectAllSheets Macro
' Macro recorded 12/11/2005 by David L Perkins
'
' Keyboard Shortcut: Ctrl+u
'
Application.ScreenUpdating = False

Dim password As String
Dim ws As Object

password = Application.InputBox(prompt:="What Is The Password?", Type:=2)

Application.ThisWorkbook.Unprotect (password)

If password = "" Then
'do nothing
Else

For Each ws In Sheets
ws.Activate
ActiveSheet.Unprotect (password)
Next ws
End If

Worksheets("Global & Monthly Inputs").Activate
Range("A1").Select

Application.ScreenUpdating = True

End Sub

Thanks for the help!

 

[Guest]

I found the problem. When using sheets, it looks at HIDDEN worksheets as
well, and there was one hidden that did not have the proper password.

Thanks much again!

David