Protect Chart Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
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
 
Thanks to each of you. I did not change my WorkSheets to Sheets. This was the
problem. Thanks again!
 
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!
 
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
 
Back
Top