Home > Pivot > Macros > Protection Pivot Table Restrict - Data Model Use macros to restrict and enable pivot table features, for pivot tables based on the Excel Data Model. For protection and restrictions on normal pivot table (not Data Model), go to the Pivot Table Protection page.

Introduction

These macros will adjust some of the pivot table settings, to prevent people from making some types of changes to a pivot table. For example, hide the drop down arrows in field headings, to disable selections in those fields.

Most of these macros are for Data Model pivot tables only, because they have some features and commands that are different from Normal pivot tables.

The sample file has a Data Model pivot table, and a Normal pivot table, and macros for each type of pivot table. There are also macros that check the type of pivot table, and run the appropriate macro.

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.

This macro removes the drop down arrows from all the Row fields and all the Report Filter fields.

NOTE: This macro will work with both Data Model and Normal pivot tables.

Sub DisableSelectionPageRow()
'select a pivot table cell, 
'   then run this macro
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveCell.PivotTable
  For Each pf In pt.PageFields
      pf.EnableItemSelection = False
  Next
  For Each pf In pt.RowFields
      pf.EnableItemSelection = False
  Next
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, in all the Row fields and Report Filter fields.

NOTE: This macro will work with both Data Model and Normal pivot tables.

Sub EnableSelectionPageRow()
'select a pivot table cell, 
'   then run this macro
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PageFields
      pf.EnableItemSelection = True
  Next
  For Each pf In pt.RowFields
      pf.EnableItemSelection = True
  Next
End Sub

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.

Note: This macro is for Data Model pivot tables only

Sub RestrictPivotTable_DM()
'select a pivot table cell,
'   then run this macro
Dim pf As PivotField
Dim wb As Workbook
Dim pt As PivotTable
On Error Resume Next

Set wb = ActiveWorkbook
Set pt = ActiveCell.PivotTable

wb.ShowPivotTableFieldList = False

With pt
  .EnableWizard = False
  .EnableDrilldown = False
  .EnableFieldDialog = False
  .PivotCache.EnableRefresh = False
  For Each pf In .PivotFields
    If pf.Name <> "Data" And _
          pf.Name <> "Values" Then
      If .IsCalculated = False Then
        With pf
          .DragToPage = False
          .DragToRow = False
          .DragToColumn = False
          .DragToHide = False
        End With
      End If
    End If
  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.

Note: This macro is for Data Model pivot tables only

Sub AllowPivotTable_DM()
'select a pivot table cell,
'   then run this macro
Dim pf As PivotField
Dim wb As Workbook
Dim pt As PivotTable
On Error Resume Next

Set wb = ActiveWorkbook
Set pt = ActiveCell.PivotTable

wb.ShowPivotTableFieldList = True

With pt
  .EnableWizard = True
  .EnableDrilldown = True
  .EnableFieldDialog = True
  .PivotCache.EnableRefresh = True
  For Each pf In .PivotFields
    If pf.Name <> "Data" And _
          pf.Name <> "Values" Then
      If .IsCalculated = False Then
        With pf
          .DragToPage = True
          .DragToRow = True
          .DragToColumn = True
          .DragToHide = True
        End With
      End If
    End If
  Next pf
End With
End Sub

Test Pivot Table Type

In the sample file, there are a few macros with names that end with _SelPT. Those macros test the pivot table, to see if it is OLAP-based (Data Model). Then, it runs another macro, based on the result of that OLAP test.

For example, the following macro checks the OLAP property of the selected pivot table. Then, based on that result, runs the RestrictPivotTable_DM macro or the RestrictPivotTable_Normal macro.

Sub Restrict_SelPT()
'runs another macro
'based on pivot table type

Dim pt As PivotTable
Dim bOLAP As Boolean
Set pt = ActiveCell.PivotTable
bOLAP = pt.PivotCache.OLAP

If bOLAP = True Then
  RestrictPivotTable_DM
Else
  RestrictPivotTable_Normal
End If

End Sub

Download the Sample File

Download the Pivot Table Block Features Data Model. workbook. 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.

More Pivot Table Tutorials

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Table Protection

Grouping Data

Multiple Consolidation Ranges

Running Totals

Summary Functions

Clear Old Items in Pivot Table