Update pivot data in protected sheet on open

matttan

New Member
Joined
Jan 13, 2005
Messages
39
Hi all,

I have two sheets that have pivot tables on them. I want the data to update when the workbook is opened. The sheets need to otherwise be protected. When I try and update with protection on, it error-messages me. I've tried putting the following code in "ThisWorkbook":

Code:
Private Sub Workbook_Open()
    
' Unprotect sheets
    Sheets("Analysis").Select
    ActiveSheet.Unprotect "password"
    Sheets("Commission Information").Select
    ActiveSheet.Unprotect "password"
    
' Update pivot info
    ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True
    
' Protect sheets and finish on intro
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    Sheets("Analysis").Select
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    Sheets("Intro").Select

End Sub

As far as I can tell, this should unprotect the relevant sheets (the two that get unprotected are the only ones with pivot tables on them - all feed off the first pivot table that is on 'analysis'), update the data, and re-protect the sheets. However, when I do it, I get the same message that I would when I have one sheet unprotected and the other protected, and I try to update the data on the unprotected sheet . Incidentally, it does follow the last command - to go to the 'Intro' sheet.

Any ideas???
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
matttan,

I'm afraid that the uploaded code seems has some syntax errors. Try this code and let us know if you still get an error.

Code:
Private Sub Workbook_Open()
    
' Unprotect sheets
    Sheets("Analysis").Unprotect "password"
    Sheets("Commission Information").Unprotect "password"
    
' Update pivot info
    ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True
    
' Protect sheets and finish on intro
    Sheets("Commission Information").Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Analysis").Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("Intro").Select
End Sub
 

matttan

New Member
Joined
Jan 13, 2005
Messages
39
Thanks for the clean-up of the syntax. Unfortunately I still get the error message. I don't even know how to debug and see if it unprotects the sheets properly. To test it further, I tried setting up a pivot table with some data in another spreadsheet, that was clean of any other macros, and only the sheet with the pivot table on was protected. Here is the code:

Code:
Private Sub Workbook_Open()
    
' Unprotect sheets
    Sheets("Sheet4").Unprotect "password"
    
' Update pivot info
    ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True
    
' Protect sheets and finish on intro
    Sheets("Sheet4").Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowUsingPivotTables:=True
    Sheets("500k batch").Select

End Sub

I got the error mesage on this one as well. However, when I tried it with the re-protect disabled, it did work. I went back to my original spreadsheet and tried it with the re-protect disabled, and it also worked there. It would seem that for some reason it's enabling the protection again before the pivot table is updated. Is there some sort of code that I can insert that says 'wait until procedure/update is performed'?

Thanks!
 

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Hi matttan,
matttan said:
I don't even know how to debug and see if it unprotects the sheets properly.

Place the cursol in the first line of your code - "Private Sub Workbook_Open()" then press F8 key.
You can execute your code step by step.

matttan said:
Is there some sort of code that I can insert that says 'wait until procedure/update is performed'?

Please try something like this.

Code:
    ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen = True
    Do
        DoEvents
    Loop Until ActiveWorkbook.PivotCaches(1).RefreshOnFileOpen
    
    'Place the protect wks code here
 

matttan

New Member
Joined
Jan 13, 2005
Messages
39

ADVERTISEMENT

OK then...

I tried the suggested code and still got the error message. I've got a different 'refresh' command in there now (most of them seem to do the same thing), but I've also included a message box to break the running of the code. Here's the code now:

Code:
Private Sub Workbook_Open()
    
' Unprotect sheets
    Sheets("Analysis").Unprotect "password"
    
' Update pivot info
    Set pvtTable = Worksheets("Analysis").Range("A5").PivotTable
        pvtTable.RefreshTable
    
    MsgBox "Pivot data updated"

' Protect sheets and finish on intro
    Sheets("Intro").Select
    Sheets("Analysis").Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _

End Sub

Now when I open the book, the pivot data is updated. However I still get my error message! Not quite sure what the problem is. I'm not sure if I can get around the error message being generated, but given the code is basically doing what it needs to, is it possible to put in some code to automatically click 'OK' on the error message?

Also, I've tried running the code with breaks, and the error only comes on the last piece of code - where I re-protect the sheet. Does this provide any hints as to why the error occurs?

Thanks again.
 

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
matttan, what kind of error message do you get at the protection part?
 

matttan

New Member
Joined
Jan 13, 2005
Messages
39
Text of the message is:

That command canot be performed while a protected sheet contains another pivot table report based on the same source data.

To remove protection from the sheet that has the other report...etc

Also of interest...I have another macro in a separate spreadsheet, that opens this file. When I run it in break mode, I only get the above message either (a) at the end of the code, or (b) when I stop the debug.
 

Forum statistics

Threads
1,147,679
Messages
5,742,575
Members
423,738
Latest member
AshleyKitsune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top