Refresh and update of Pivot tables on protected sheets on opening wkbk.

[rob nobel]

I have the following code run on opening the file, but it says, "Cannot edit
pivot table on protected sheet. That dialog box comes up twice, immediately
after each other. After clicking OK, the file opens still having done the
full procedure!??
The strange thing is that if I execute the code via VBA editor it works
perfectly without the messages.
I can't see what the problem is. Please help
When I open VBA editor immediately after this, it always opens at another
worksheet module ("Sheet9"), that only has this code in it.
Sub RefreshPivot()
Call ThisWorkbook.UpdateAllPivotTables
End Sub

The "ThisWorkbook" code is as follows:
Private Sub Workbook_Open()
Call UpdateAllPivotTables
'Application.ScreenUpdating = False
With Sheets("ALL A-C")
.EnableAutoFilter = True
..Protect Password:="", UserInterfaceOnly:=True
End With
'Application.ScreenUpdating = True
End Sub

Sub UpdateAllPivotTables()
'Debra Dalgleish's gets rid of unused items in PivotTable
' AND update pivot tabel (based on MSKB Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer 'I don't think this dim is required.
'Application.ScreenUpdating = False
With Sheet5
..Unprotect Password:=""
End With
With Sheet6
..Unprotect Password:=""
End With
With Sheet9
..Unprotect Password:=""
End With
'Application.ScreenUpdating = False
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
With Sheet5
..Protect Password:="", UserInterfaceOnly:=True
End With
With Sheet6
..Protect Password:="", UserInterfaceOnly:=True
End With
With Sheet9
..Protect Password:="", UserInterfaceOnly:=True
End With
Application.ScreenUpdating = True
End Sub

Thankyou for any help,
Rob

 

[Dave Peterson]

Just a guess and some comments.

comments first:
I think you'd be better off moving the UpdateAllPivotTables and RefreshPivot
into a general module. (Remember to adjust the call's to them.)

I like to only put events and code for controls from the controltoolbox toolbar
in the worksheet module.

(But I don't think that would cause your problem.)

I've seen some posts by Tom Ogilvy that say that he's had some trouble using the
..unprotect against worksheets without activating the sheet first. (They might
have even said when they were in (or called by???) the workbook_open event.)

You may want to try:

with sheet5
.activate
.Unprotect Password:=""
end with

If that doesn't help, try putting Stop as the first statement of your
workbook_open code.

Save and close your workbook and reopen. Maybe you'll see something when it's
stepping through it (maybe...).

 

[rob nobel]

Thanks for that suggestion Dave. I did wonder if there was a way to stop
execution of a macro on opening the wkbk.
I added the ".activate" preceeding both .protect and .unprotect sections of
the code.
I ran the procedure which works faultlessly until the last line is reached
(which is: End Sub) and then the dialog box stating, "Cannot edit pivot
table on protected sheet" shows. The actions the procedure sets out to do
are actually completed BUT... then it says it can't do it. On checking the
pivot table, the data has actually been updated even though it says it can't
do it! (The procedure steps past the unprotection and protection without
breaking.) It's a mystery to me!
If I remove Stop and rerun the procedure it works fine without the message
box appearing. AAAARRGGHHH!!
Just a question re where to place code. Why in a different module. Is it
for a good programming reason?
Rob

 

[Dave Peterson]

General modules for general routines.

Worksheet modules for events and controls.

Seems like nice organization and it makes calls to the general routines easier.

 

[rob nobel]

Thanks

General modules for general routines.

Worksheet modules for events and controls.

Seems like nice organization and it makes calls to the general routines easier.