Updating PivotTable using command button on a locked sheet

[kidwispa]

Hi all,

I currently have a command button within a protected sheet that when pressed runs a macro that refreshes the data in a pivot table and pivot chart. The problem is when I open the file it gives the error message "Cannot edit pivot table on protected sheet". When I unprotect the sheet, then protect it again ticking the "Use pivot table reports" option the macro works fine and I can go in and use the full functionality of the pivot chart as well. However, when I save the file it saves the fact that the sheet is password protected but not the 'use pivot table reports' bit, so when I reopen it the same error message appears.


My question is, can anyone help me write some code that, when the file is opened, ensures that the above error message doesn't happen?

 

[kidwispa]

Update - I've tried using the code below in the Workbook section of ThisWorkbook...

Private Sub Workbook_Open()
Worksheets("Results").Unprotect Password:="Password"
 
    Worksheets("Results").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowUsingPivotTables:=True
   Worksheets("Results").EnableSelection = xlUnlockedCells
 
Worksheets("Results").Protect Password:="Password"
 
End Sub

However the error message still comes up and the sheet gets protected but without the password assigned. Is it possible for this to work BEFORE the error message appears?