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.

pivotenablewizard01

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.

pivotenablewizard02

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.

pivotenablewizard03

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.

pivotpowerpremrestrict01

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

__________________

This entry was posted in Programming. Bookmark the permalink.

4 Responses to Prevent Changes to Pivot Table Setup

  1. Arun says:

    you can go to pivot table option and click data then untick Save source data with file will result the same

  2. Pingback: Help with YTD fomula for Sales

  3. bernat says:

    hey, amazing!
    looked around long time before finally finding the right answer

  4. Jonas says:

    Really nice!
    Thanks for sharing

Leave a Reply

Your email address will not be published. Required fields are marked *