Workbook protection with an exception

[K. Georgiadis]

I have the following code in "This Workbook" to:


momentarily unprotect the Pivot Table so that it can be refreshed
re-protect all worksheets with the listed exceptions

What kind of an instruction would I need to include so that all worksheets are protected with the sole exception of the Sheet "Charts?"



Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pt As PivotTable
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="mysecretword"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
Password:="mysecretword"
Next
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

[Jacob Hilderbrand]

Try this:


For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Charts" Then
ws.Unprotect Password:="mysecretword"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
Password:="mysecretword"
End If
Next

[K. Georgiadis]

It didn't work for me....I left in the original variable declarations and the Screen Updating command which I presume was OK

[Jacob Hilderbrand]

Try this.


Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pt As PivotTable
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Charts" Then
ws.Unprotect Password:="mysecretword"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
Password:="mysecretword"
End If
Next
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

[K. Georgiadis]

looks the same as the previous one. Should the qualifier


If ws.Name <> "Charts" Then

be placed before:


ws.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, _
AllowFiltering:=True, _
Password:="mysecretword"


?

[Jacob Hilderbrand]

It is there. The If statement is the first line checked in the Loop. If it is False (i.e., ws.Name = "Charts") then it would do directly to the End If. So it will not run any of the code.

[K. Georgiadis]

well back to the drawing board because this doesn't do the trick :dunno

Maybe leaving the worksheet "Charts" protected is not that big a deal after all :)

[Jacob Hilderbrand]

It should work fine if the sheet name is actually "Charts". Make sure that there are no trailing spaces (i.e., "Charts "). Or you can attach the workbook and I will take a closer look.

[K. Georgiadis]

There are no trailing spaces and it is really called "Charts". As to sending the workbook, other than the fact that contains a lot of 3rd party proprietary information, it is 4 megs! I'll table this for now...Thanks for your help

OK, I adapted your code for the attached small workbook that I created. The worksheet "Chart" gets protected along with the rest. What am I doing wrong?
It's bed time for me. Talk to you soon and thanks again for your help

[Jacob Hilderbrand]

Seems to work fine. Just make sure that the sheet "Charts" is unprotected to begin with.

[K. Georgiadis]

I hadn't done that! It works now.

Btw, I have a quick and unrelated question: when working in the Immediate Window, I will sometimes add a new worksheet with:


Worksheets.Add

What is the syntax if I wanted to add 3 sheets at once? I know there must be a Count variant but I have trouble finding the syntax in the online help.

Thanks!!!

[XL-Dennis]

Worksheets.Add Count:=5


Kind regards,
Dennis

[K. Georgiadis]

Thanks Dennis!

[XL-Dennis]

You're welcome but You should also thanks Jake ;)

[K. Georgiadis]

I thanks Jake all the time. He has been extremely helpful to me and many others