Macro 73: Apply Pivot Field Restrictions
Like PivotTable restrictions, pivot field restrictions enable us to restrict the types of actions our users
can take on the pivot fields in a PivotTable. The macro outlined in this section demonstrates some of
the protection settings available via VBA.
How it works
The PivotField object exposes several properties that allow you (the developer) to restrict differ-
ent features and components of a PivotTable.
➤ DragToPage: Setting this property to False prevents the users from dragging any pivot
field into the Report Filter area of the PivotTable.
➤ DragToRow: Setting this property to False prevents the users from dragging any pivot
field into the Row area of the PivotTable.
➤ DragToColumn: Setting this property to False prevents the users from dragging any pivot field
into the Column area of the PivotTable.
➤ DragToData: Setting this property to False prevents the users from dragging any pivot
field into the Data area of the PivotTable.
➤ DragToHide: Setting this property to False prevents the users from dragging pivot fields
off the PivotTable. It also prevents the use of the right-click menu to hide or remove pivot
fields.
➤ EnableItemSelection: Setting this property to False disables the drop-down lists on each pivot
field.
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 Macro73()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘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 Pivot Field Restrictions
For Each pf In pt.PivotFields
pf.EnableItemSelection = False
pf.DragToPage = False
pf.DragToRow = False
pf.DragToColumn = False
pf.DragToData = False
pf.DragToHide = False
Next pf
End Sub
1. Step 1 declares two object variables, using pt as the memory container for our PivotTable
and pf as a memory container for our pivot fields. This allows us to loop through all the
pivot fields in the PivotTable.
2. Set 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 tar-
get pivot.
3. Step 3 checks to see whether the pt variable is filled with a PivotTable object. If the pt vari-
able 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 via a message box, and
then exits the procedure.
4. Step 4 of the macro uses a For Each statement to iterate through each pivot field. Each
time a new pivot field is selected, we apply all of our pivot field 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.
