Sheet protection code conflicts with Pivot Table "auto refresh"

[Guest]

I received the following code from Debra Dalgleish, to password protect all
worksheets, setting certain exceptions for objects, etc.


Public Sub ProtectAll()

Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub

It works well, except for one thing: when I open the file, I get an error
message which emanates from the fact that I have selected "refresh
automatically" in the Pivot Table Options (the problem disappears when I
deselect the auto refresh box). I really don't want to disable that feature,
therefore, for the time being I go to the worksheet containing the Pivot
Table and manually remove all protection.

I presume that there is a way to resolve this conflict with VBA.

Thanks for your help.

 

[Debra Dalgleish]

Instead of setting the pivot tables to 'refresh on open', you could
refresh the pivot tables programmatically. Create a macro to unprotect
the sheets, refresh the pivot tables, and reprotect the sheets.

 

[Guest]

Can the following code (which I believe you also provided in recent months)
be used in tandem with the sheet protection code:

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="SecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="SecretWord"
Next

End Sub

If so, I presume the above code would go into "This Workbook." However I
already have the following short code in "This Workbook" and I am confused
whether or not the two pieces will need to be a single, continuous
"Workbook_open" code:

Private Sub Workbook_Open()
UserForm1.Show
Sheets("Instructions").Select
Range("A1").Select
End Sub

Thank you for your help.

 

[Debra Dalgleish]

Yes, you can only have one Workbook_Open procedure in the workbook, so
you should combine them.

Add your three existing lines to the end of the longer procedure, and
store the new procedure in the ThisWorkbook module.

 

[Guest]

thank you. Problem solved

Yes, you can only have one Workbook_Open procedure in the workbook, so
you should combine them.

Add your three existing lines to the end of the longer procedure, and
store the new procedure in the ThisWorkbook module.

 

[Guest]

This code worked perfectly for me, however anyone opening the file has only
to look at the code to find out what the password is. Is there any way to
avoid this?

 

[Dave Peterson]

You can protect your project.

Next time you're in the VBE, select your project.
tools|vbaproject properties|protection tab
Lock project for viewing and give it a memorable password.

(Be aware that the password won't stop the really curious.)