Update pivot data in protected sheet on open

[matttan]

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

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???

 

[Colo]

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.

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]

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:

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]

Hi matttan,

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.

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

Please try something like this.

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

 

[matttan]

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:

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]

matttan, what kind of error message do you get at the protection part?

 

[matttan]

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.