Macro 72: Apply PivotTable Restrictions
We often send PivotTables to clients, coworkers, managers, and other groups of people. In some cases,
we’d like to restrict the types of actions our users can take on the PivotTable reports we send them. The
macro outlined in this section demonstrates some of the protection settings available via VBA.
How it works
The PivotTable object exposes several properties that allow you (the developer) to restrict differ-
ent features and components of a PivotTable:
➤ EnableWizard: Setting this property to False disables the PivotTable Tools context menu
that normally activates when clicking inside of a PivotTable. In Excel 2003, this setting dis-
ables the PivotTable and Pivot Chart Wizard.
➤ EnableDrilldown: Setting this property to False prevents users from getting to detailed data by
double-clicking a data field.
➤ EnableFieldList: Setting this property to False prevents users from activating the field list or
moving pivot fields around.
➤ EnableFieldDialog: Setting this property to False disables the users’ ability to alter the pivot
field via the Value Field Settings dialog box.
➤ PivotCache.EnableRefresh: Setting this property to False disables the ability to refresh the
PivotTable.
You can set any or all of these properties independently to either True or False. In this macro, we
apply all of the restrictions to the target PivotTable.
Sub Macro72()
‘Step 1: Declare your Variables
Dim pt As PivotTable
‘Step 2: Point to the PivotTable in the activecell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Apply PivotTable Restrictions
With pt
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
End With
End Sub
1. Step 1 declares the pt PivotTable object variable that serves as the memory container for
our PivotTable.
2. Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
3. Step 3 checks to see if the pt variable is filled with a PivotTable object. If the pt variable is
set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, the macro notifies the user in a message box, and
then we exit the procedure.
4. In the last step of the macro, we are applying all PivotTable restrictions.
How to use it
You can implement this kind of a macro in a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
