Allow or Block Pivot Table Features

After you set up an Excel pivot table, use macros to allow or block pivot table features and commands. That should discourage other people from making layout changes, which could affect other parts of your Excel workbook.

Pivot Table Settings

There are some pivot table settings that you can turn on or off manually. For example, in the PivotTable Options window, on the Data tab, you can remove the check mark from Enable Show Details.

When that option is turned off, you can’t double-click on a Value cell, to create a list of all the data for that value.

Using Macros

Of course, if you turn that setting off manually, someone else could just as easily turn it back on. To help prevent that, use macros to change some of the pivot table settings, so it’s not so easy to undo them!

For example,  this macro turns off the PivotTable Options command.

Sub PTOptionsOff()
  ActiveCell.PivotTable _
    .EnableWizard = False
End Sub

After the EnableWizard property is set to False, nobody can quickly adjust your pivot table options. The PivotTable Options command is dimmed out, in the menu that appears when you right-click a pivot table cell

Block Pivot Table Features

There are many other settings that you can turn on, by using macros to block pivot table features. There are sample macros on my Contextures website – go to the Pivot Table Protection page to see the code and download the sample workbook with macros.

However, some of those macros were written before the Data Model was introduced in Excel 2013. Pivot tables that use the Data Model are OLAP-based, and some of their features and properties  are slightly different from normal pivot tables.

For example, the DM command to hide the Pivot Table Field List

wb.ShowPivotTableFieldList = False

is different from the normal pivot table command.

pt.EnableFieldList = False

Data Model Macro

To help you block pivot table features in the Data Model (DM) pivot tables, I’ve created a few new macros.

For example, the following macro turns off many pivot table settings.  It hides the field list, disables a the field settings and pivot table options dialog boxes, prevents refreshes, and a few other things.

Use this macro with Data Model pivot tables only – you’ll get an error with normal pivot tables.

NOTE: There is another macro in the sample file that changes the settings to True, to enable the features again

Sub RestrictPivotTable_Normal()

'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

With pt
  .EnableWizard = False
  .EnableDrilldown = False
  .EnableFieldList = 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
          .DragToData = False
          .DragToHide = False
        End With
      End If
    End If
  Next pf
End With
End Sub

Get the Excel Workbook

To see more macros that block pivot table features with the Data Model, go to the Pivot Table Data Model Restrictions page on my Contextures site.

That page has a download link for the sample workbook, which contains test pivot tables, and the macros. The zipped Excel file is in xlsm format, and be sure to enable macros when you open the workbook, if you want to test the macros.

____________________________

Block Pivot Table Features

_______________________