Prevent Changes to Pivot Table Setup

After you set up a pivot table, you might want to prevent other people from making changes to the way that it is set up. With a bit of programming, you can restrict what happens to a pivot table.

Usually, if you click on a pivot table cell, a couple of additional tabs appear on the Ribbon – Analyze and Design. You’ll also see a field list at the right, where you can add and remove fields from the pivot table layout.

However, after I run the code that disables these features, the Ribbon tabs don’t appear when the restricted pivot table is selected. This pivot table is on the same worksheet as the previous one, and the worksheet is not protected.

If you right-click on the restricted table, the PivotTable Options command is dimmed out – you can’t get into that dialog box to change the settings.

Use the PivotPower Premium Commands

If you have a copy of my PivotPower Premium add-in, you can turn pivot table restrictions on and off by using the Ribbon commands.

If you don’t have a copy of the add-in, you can use the code shown below to turn restrictions on or off for any pivot table.

Restrict Pivot Table Setup Changes Code

To hide the pivot table Ribbon tabs, and block the use of the PivotTable Options window, you can select a pivot table cell, and run the following code. It changes the EnableWizard property to FALSE, which restricts access to the Ribbon tabs, Field List, and Options window.

Sub PTWizardOff()
Dim pt As PivotTable
    
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo errHandler

If Not pt Is Nothing Then
   pt.EnableWizard = False
Else
   MsgBox "Please select a cell in a pivot table"
End If

exitHandler:
  Set pt = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

Restrict Pivot Table Setup Changes Code

To restore the Ribbon tabs and PivotTable Options window, select a cell in the pivot table, and run the following code. It changes the EnableWizard property to TRUE, which allows access to the Ribbon tabs, Field List, and Options window.

Sub PTWizardOn()
Dim pt As PivotTable
    
On Error Resume Next
Set pt = ActiveCell.PivotTable
On Error GoTo errHandler

If Not pt Is Nothing Then
   pt.EnableWizard = True
Else
   MsgBox "Please select a cell in a pivot table"
End If

exitHandler:
  Set pt = Nothing
  Application.ScreenUpdating = True
  Exit Sub

errHandler:
  GoTo exitHandler
End Sub

__________________