Home > Pivot > Macros > Protection Pivot Table Restrict - Data ModelUse 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. |
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.
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
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
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
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
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 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.
Last updated: March 5, 2023 2:59 PM