Refresh Pivot tables in protected worksheet - Automatically

[tebrica]

Hi Everyone,

In a workbook with multiple pages, there is one with 2 pivot tables that needs to have functionality of automatic refresh when you come to that page (simply by shifting pages).
Currently, I do have a code that does that, however it needs to be upgraded/changed in order to allow the same thing, but in protected sheet.
On top of pivot tables there will be predicted fields that can be subject of the change in protected sheet (i.e. "unlocked"), however I don't want to allow any modification of the remained cells, nor pivot table itself (by including/excluding fields for example).

Attached is document with code (and detailed guidance).

Does someone know how this can be done?

Many thanks,
Tebr

[Artik]

Try replacing the procedure with the following:

Private Sub Worksheet_Activate()
    Dim pt          As PivotTable

    Me.Unprotect Password:=""

    Application.EnableEvents = False

    For Each pt In Me.PivotTables
        pt.RefreshTable
    Next pt

    Me.Protect Password:="", DrawingObjects:=True, _
               Contents:=True, Scenarios:=True, _
               AllowUsingPivotTables:=True

    Application.EnableEvents = True
End Sub

Artik

[tebrica]

Thanks Artik, this is close, but PivotTable Fields are modifiable which I don't want. Is it possible to remove/protect/hide "Field List" for example, or something similar?

Tebr

[tebrica]

I've figured it out. Replace AllowUsingPivotTables:=True, with AllowUsingPivotTables:=False
Then it works smoothly.

Thanks a lot.

Tebr

Private Sub Worksheet_Activate()
    Dim pt          As PivotTable

    Me.Unprotect Password:=""

    Application.EnableEvents = False

    For Each pt In Me.PivotTables
        pt.RefreshTable
    Next pt

    Me.Protect Password:="", DrawingObjects:=True, _
               Contents:=True, Scenarios:=True, _
               AllowUsingPivotTables:=False

    Application.EnableEvents = True
End Sub