Contextures

Excel Pivot Table Protection

Use macros to protect a pivot table, or specific features of a pivot table. Also, use code to refresh a pivot table on a protected worksheet

Disable Selection

To protect the pivot table selections from being changed, 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.

To see the steps in a video, go to the Pivot Table Filter Videos page.

selection arrows hidden

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

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

Enable Selection

The following macro enables the features that were turned off in the DisableSelection macro, shown above. The drop down arrows are visible again.

To see the steps in a video, go to the Pivot Table Filter Videos page.

selection arrows visible

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

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, 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
selection arrows visible

Restrict PivotTable

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.

Refresh command not available

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

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

Allow PivotTable

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.

move field to different area

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

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

Change Macro to Run on All Pivot Tables or Sheets

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.

All pivot tables on the active sheet

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

All pivot tables in the active workbook

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

Refresh Pivot Table on Protected Sheet

When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables.

refresh pivot protected sheet

However, once the sheet is protected, you can't create a new pivot table. Also, you won't be able to refresh a pivot table on the worksheet, because that feature is disabled on a protected sheet. If you right-click in a pivot table cell, the Refresh command is disabled.

refresh pivot protected sheet

You could manually unprotect the worksheet, refresh the pivot table, and then protect the sheet again.

Another option, if you're trying to refresh the pivot table programmatically, is to unprotect the worksheet, refresh the pivot table, and then protect the sheet again.

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.

Refresh Pivot Table Connected to Protected Sheet

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."

refresh pivot protected sheet

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

Download the Sample File

Download the sample pivot table tutorial file

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table

Search Contextures Sites

 

 

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

 

 

Last updated: May 17, 2016 7:08 PM