Home > Pivot > Layout > Protection Excel Pivot Table ProtectionUse macros to protect a pivot table, or specific features of a normal pivot table (not Data Model). Also, use code to refresh a pivot table on a protected worksheet For protection and restrictions on Data Model pivot tables, go to the Pivot Table Data Model Restrictions page |
You can protect a worksheet and allow users to make some changes to the pivot tables. However, there are some restrictions on the pivot table functions, when the sheet is protected. Watch this video to see how to set up the protections, and there are written details below the video.
When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables.
Even if you allow the use of pivot tables on a protected worksheet, there are limitation to what you can do. NOTE: Pivot tables on other sheets may be limited too, if they are based on the same pivot cache.
The following pivot table commands (and others) are disabled when a sheet is protected:
To use these features, temporarily unprotect the worksheet.
To protect the pivot table selections from being changed, you can change a pivot table setting. Or, you can use a macro to disable selection. The dropdown arrows will disappear from the field buttons, and you won't be able to change the displayed items.
Both methods -- the manual setting and the macro -- are shown in this video, and there are written steps, along with the macro code, below the video.
Video Timeline
In the sample file, there are buttons that you can click, to run the Disable Selection macros, and other macros.
Note: In the code below, instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields, to disable selection for a specific part of the pivot table
Sub DisableSelection() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields pf.EnableItemSelection = False Next pf End Sub
Note: You could also disable selection for a specific field.
In this example, the arrow is removed from the first Report Filter field (PageFields).
Sub DisableSelectionSelPF() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PageFields(1) pf.EnableItemSelection = False End Sub
The following macro enables the features that were turned off in the DisableSelection macro, shown above. The drop down arrows are visible again.
The steps are shown in the video above, and the code is in the next section, below.
In the sample file, there are buttons that you can click, to run the Enable Selection macros, and other macros.
Note: In the code below, instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields, to enable selection for a specific part of the pivot table
Sub EnableSelection() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PivotFields pf.EnableItemSelection = True Next pf End Sub
Note: You could also enable selection for a specific field. In this example, the arrow is shown only for the "Region" field.
To do this, first, run the DisableSelection macro (above), then run this EnableSelectionSelPF macro.
Sub EnableSelectionSelPF() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFields("Region") pf.EnableItemSelection = True End Sub
You can use a macro to disable many of the features in a pivot table. The following code prevents users from dragging any of the fields to a different location, or off the pivot table. It also prevents them from using the Field List, Field Settings, or Drilldown, and disables the Refresh feature.
In the sample file, there are buttons that you can click, to run the Restrict Pivot Table macro, and other macros.
Note: In the code below, instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields, to prevent dragging in a specific part of the pivot table
Sub RestrictPivotTable() 'pivot table tutorial by contextures.com Dim pf As PivotField On Error Resume Next With ActiveSheet.PivotTables(1) .EnableDrilldown = False .EnableFieldList = False .EnableFieldDialog = False .PivotCache.EnableRefresh = False For Each pf In .PivotFields With pf .DragToPage = False .DragToRow = False .DragToColumn = False .DragToData = False .DragToHide = False End With Next pf End With End Sub
The following macro enables the features that were turned off in the RestrictPivotTable macro, shown above. For example, you are able to move the fields to a different area.
In the sample file, there are buttons that you can click, to run the Allow Pivot Table macro, and other macros.
Note: In the code below, instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields, to allow dragging in a specific part of the pivot table
Sub AllowPivotTable() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next Set pt = ActiveSheet.PivotTables(1) With pt .EnableDrilldown = True .EnableFieldList = True .EnableFieldDialog = True .PivotCache.EnableRefresh = True For Each pf In pt.PivotFields With pf .DragToPage = True .DragToRow = True .DragToColumn = True .DragToData = True .DragToHide = True End With Next pf End With End Sub
The macros in the previous sections are set up to run on the first pivot table on the active worksheet. You can change the macros slightly, so they run on all pivot tables on the active sheet, or all pivot tables, on all sheets in the active workbook.
To have the macro run on all pivot tables on the active sheet, remove the line:
Set pt = ActiveSheet.PivotTables(1)
and replace it with
For Each pt in ActiveSheet.PivotTables
At the end of the section, create a new line
Next pt
For example, here is the EnableSelection macro, revised to run on all pivot tables in the active sheet.
Sub EnableSelectionAllPT() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField On Error Resume Next For each pt in ActiveSheet.PivotTables For Each pf In pt.PivotFields pf.EnableItemSelection = True Next pf Next pt End Sub
NOTE: If you are using "For Each pf in pt. PivotFields", add an If...Then section, to ensure that the code doesn't run on the "Values" field (called "Data" in older versions), which is created when there are multiple value fields. The example below will run on all pivot tables on the active sheet..
Sub AllowPivotTableSheet() Dim pt As PivotTable Dim pf As PivotField Dim ws As Worksheet Set ws = ActiveSheet For Each pt In ws.PivotTables With pt .EnableWizard = True .EnableDrilldown = True .EnableFieldList = True .EnableFieldDialog = True .PivotCache.EnableRefresh = True For Each pf In pt.PivotFields '<== uses .PivotFields With pf If .Name <> "Values" And .Name <> "Data" Then .DragToPage = True .DragToRow = True .DragToColumn = True .DragToData = True .DragToHide = True End If End With Next pf End With Next pt End Sub
To have the macro run on all pivot tables, on all sheets, in the active workbook, add the line:
dim ws as Worksheet
At the start of the code, add this line
For Each ws in ActiveWorkbook.Worksheets
At the end of the section, create a new line
Next ws
For example, here is the EnableSelectionAllPT macro, revised to run on all pivot tables, on all sheets, in the active workbook.
Sub EnableSelectionAllPTAllSheets() 'pivot table tutorial by contextures.com Dim pt As PivotTable Dim pf As PivotField Dim ws as Worksheet On Error Resume Next For each ws in ActiveWorkbook.Worksheets For each pt in ActiveSheet.PivotTables For Each pf In pt.PivotFields pf.EnableItemSelection = True Next pf Next pt Next ws End Sub
When a worksheet is protected, you can't refresh the pivot tables on that sheet. You could manually unprotect the worksheet, refresh the pivot table, and then protect the sheet again.
If you're trying to refresh the pivot table with a macro, add code to unprotect the worksheet, refresh the pivot table, and then protect the sheet again. This macro shows an example.
Sub UnprotectRefresh() On Error Resume Next With Activesheet .Unprotect Password:="mypassword" .PivotTables(1).RefreshTable .Protect Password:="mypassword", _ AllowUsingPivotTables:=True End With End Sub
NOTE: If other pivot tables are connected to the same pivot cache are on protected sheets, you can use the code below, to unprotect all the sheets.
If you try to refresh a pivot table, you might see a message that says: "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data."
This occurs if the pivot table that you're trying to refresh shares the same pivot cache as another pivot table, and that pivot table is on a protected sheet.
As the message says, you could manually unprotect the other sheet, and then refresh the pivot table.
Another option, if you're trying to refresh the pivot table programmatically, is to unprotect all the sheets, refresh the pivot tables, and then protect the sheets again.
Sub UnprotectRefreshAll() Dim ws As Worksheet On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Password:="mypassword" Next ws ActiveWorkbook.RefreshAll For Each ws In ActiveWorkbook.Worksheets ws.Protect Password:="mypassword", _ AllowUsingPivotTables:=True Next ws End Sub
Get the sample pivot table tutorial file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when opening the file, if you want to test the macros.
Last updated: May 23, 2023 12:26 PM