Update pivot chart on locked worksheet

[MaxxFusion]

I use a vba script to lock all my sheets in one click. I also have a vba script that updates my pivot chart automatically when i change my data.

The issue i have now is when i use the vba script to lock my sheets the chart wont update. If i try to update the table manually i get an error becuase the sheets are locked.

How can i get around this?

Here is the vba code i use to lock the sheets.

Sub protectsheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
ActiveSheet.EnableSelection = xlUnlockedCells
wsheet.Protect Password:="xxxxxxxx", AllowUsingPivotTables:=True
Next wsheet
End Sub

 

[starl]

you need to add

DrawingObjects:=False

to the protect

and, btw, your EnableSelection only applies to the activesheet - not all the sheets being looped through

 

[MaxxFusion]

I added this but now all ovjects are editable and my chart still didnt refresh. Is there a differenent way to do this?

Also what do you mean not all sheet being looped through?

 

[starl]

ActiveSheet.EnableSelection = xlUnlockedCells

- that right there applies only to the ACTIVESHEET - the sheet that is currently open, being viewed.
When you loop through sheets (which is what 'For each wsheet' is doing) - the sheets are not activated. They're just being referenced.
So, as your looping through all the sheets in the workbook, you keep .EnableSelection on the sheet that was active when you started the code.
Change ActiveSheet to wsheet if you want that line of code to run on each sheet instead.

As for the additional property, change your Protect line to:

wsheet.Protect Password:="xxxxxxxx", AllowUsingPivotTables:=True, DrawingObjects:=False

It's saying to NOT protect the DrawingObjects on the sheet.

 

[MaxxFusion]

Is that the only way to do it? I have a dashboard page that is mostly drawing objects i dont want people to mess with.

Also my pivot chart still doesnt update when i lock. I made the changes above.

Here is the pviot update vba i am using on my sheet that has the data. This works when its unlocked.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub

 

[starl]

well, the charts should update even if the sheet is protected.. the code I added was so that you could manually modify the pivot chart.
Updating the data source should update a chart, even if the chart is locked.
fyi - your _Change event could loop, technically. You're refreshing the entire workbook and so if there's a cell on the sheet that changes, then the event gets called again, etc...

So, to summarize: you have a Dashboard with various reports, charts, etc. You have code that protects all the sheets. Presumably, the data source of the dashboard, you've setup so you can still update the data. When you do so, you have it set to refresh the entire workbook. But your charts are not refreshing. And you get no errors...
But the moment you unprotect a sheet with charts and do a refresh - the charts update?

Duplicated scenario, working on workaround

 

[starl]

I didn't know that you couldn't refresh a protected sheet... seems like goof to me. Here's an update on your code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'disables events so we don't loop
UnProtectSheets
ThisWorkbook.RefreshAll
ProtectSheets
Application.EnableEvents = True 'allows events to run again
End Sub


Sub ProtectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
    wsheet.EnableSelection = xlUnlockedCells
    wsheet.Protect Password:="xxxxx", AllowUsingPivotTables:=True, userinterfaceonly:=True 'threw on userinterfaceonly, always good when you've got code running
Next wsheet
End Sub

Sub UnProtectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
    wsheet.Unprotect Password:="xxxxx"
Next wsheet
End Sub

 

[MaxxFusion]

Hmm. This seemed to work yesterday at home. Now today at work its not working. Can you confirm what macro you are using to lock the sheets in your test file?

 

[MaxxFusion]

Actually i used this to lock my sheets. That didnt make a difference. This is so stange.

Sub ProtectSheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
    wsheet.EnableSelection = xlUnlockedCells
    wsheet.Protect Password:="xxxxx", AllowUsingPivotTables:=True
Next wsheet
End Sub

 

[starl]

I tested with the code I posted. I have a small pivot table with a chart both on the same sheet, though NOT a chart sheet.
The data source is a table (listobject) on a separate sheet in the same workbook.
I replaced my protect with yours and it still works.
I could make my test file available to you. Or you could make yours available to me so I could test it on my end. There are so many ways you could have your workbook setup and it doesn't match my configuration.