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

 

[MaxxFusion]

How can I send you my workbook to take a look at?

Also is there a way to only unprotect and reprotect 5he neecassary sheets? That way it doesn't doo all of them in this case? One thing I noticed when this worked on my worksheet it took a couple seconds to complete and the screen flashed. I would like it if the user didn't know anything was happening.

 

[MaxxFusion]

Now i am really confused. You are right. I got home and tried it there. Its working as i described in my previous post above.

I am using Office 2016 at work and home. I use the same macro in my personal workbook for the protect and unprotect scripts. I have the macros in the quick launch ribbon for easy access.

What else could possibly be different?

 

[starl]

application.screenupdating = false/true should handle the flickers - but that's something we can integrate later.
Yes, we can specific the worksheets instead of looping. But I need more info - like which sheet(s). The answer affects the best answer.

Is the data source in the workbook? or is it linked to an outside data source?

 

[starl]

Not sure why the workbook worked at home but not at work. Reviewing the code found misspellings, but that would prevent it from working at home, too.
Below is the final code that does work. It combines using a collection to create a list of sheet to toggle protection on.
Setup is: data is entered on All Players; when changes are made, the workbook is refreshed, especially a pivot table and it's accompanying chart.
Code exists in various modules:

Module2 holds various subs that will be called in other modules:

'code that could get called from multiple places should be placed in normal modules
Private Protect_List As Collection
Const pwd = "xxx"

Sub ListProtectedSheets()
'list the sheets in this workbook you want to toggle protection
Set Protect_List = New Collection

Protect_List.Add Worksheets("Dashboard")
Protect_List.Add Worksheets("All Players")
Protect_List.Add Worksheets("AllPlayers Pivot")
Protect_List.Add Worksheets("AllPlayers Table")
End Sub

Sub UnProtectSheets()
Dim ws As Worksheet

For Each ws In Protect_List
    ws.Unprotect Password:=pwd
Next ws
End Sub

Sub ProtectSheets()
Dim ws As Worksheet

For Each ws In Protect_List
    With ws
        .EnableSelection = xlUnlockedCells
        .Protect Password:=pwd, AllowUsingPivotTables:=True, UserInterfaceOnly:=True
        'UserInterfaceOnly allows code to make changes to protected cell; always handy to have
        'can only be set via code, though.
    End With
Next ws
End Sub

The _Change event is on the All Players sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
'the following catches any errors and has the code jump to the errHandler
'ensuring the workbook resets properly. Else, those items are OFF (False) and can cause problems
On Error GoTo errHandler

Application.EnableEvents = False 'stops events running
Application.ScreenUpdating = False 'stops most screen flickering, but not all

'you don't need to prefix 'Module2.' - but it lets you know where the code is
Module2.UnProtectSheets
ThisWorkbook.RefreshAll
Module2.ProtectSheets

errHandler:
'what you disable, you MUST re-enable (or restart Excel, not just the workbook, but the application itself)
Application.EnableEvents = True
Application.ScreenUpdating = True

'see the error message
If Err.Number <> 0 Then
    MsgBox Err.Number & ": " & Err.Description
End If

End Sub

and finally, an Open event in the Thisworkbook module creates the collection

Private Sub Workbook_Open()
'list is created when the workbook is opened
Module2.ListProtectedSheets
End Sub