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?

"KG" wrote:

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" wrote:

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.

KG wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html