Excel -- Pivot Tables -- Protection

  1. Disable Selection
  2. Enable Selection
  3. Restrict PivotTable
  4. Allow PivotTable
Download the sample file

Disable Selection

In Excel 2002, and later versions, you can use a macro to disable selection in a pivot table. The dropdown arrows will disappear from the field buttons, and you can't change the displayed items.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub DisableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = False
  Next pf
End Sub

 

Selection Disabled
Selection Disabled

 

Enable Selection

The following macro enables the features that were turned off in the DisableSelection macro, shown above. (Excel 2002 and later versions.)

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub EnableSelection()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
  For Each pf In pt.PivotFields
      pf.EnableItemSelection = True
  Next pf
End Sub

 

Selection Enabled
Selection Enabled

Restrict PivotTable

You can use a macro to disable many of the features in a pivot table. The following code prevents users from dragging any of the fields to a different location, or off the pivot table. It also prevents them from using the Pivot Wizard, Field List, Field Settings, or Drilldown, and disables the Refresh feature.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub RestrictPivotTable()
Dim pf As PivotField
With ActiveSheet.PivotTables(1)
  .EnableWizard = False
  .EnableDrilldown = False
  .EnableFieldList = False   'Excel 2002+
  .EnableFieldDialog = False
  .PivotCache.EnableRefresh = False
  For Each pf In .PivotFields
    With pf
      .DragToPage = False
      .DragToRow = False
      .DragToColumn = False
      .DragToData = False
      .DragToHide = False
    End With
  Next pf
End With

End Sub

 

Dragging Disabled
Dragging Disabled

 

Allow PivotTable

The following macro enables the features that were turned off in the RestrictPivotTable macro, shown above.

Note: Instead of .PivotFields, you can use .RowFields, .ColumnFields or .PageFields

Sub AllowPivotTable()
Dim pt As PivotTable
Dim pf As PivotField
Set pt = ActiveSheet.PivotTables(1)
With pt
  .EnableWizard = True
  .EnableDrilldown = True
  .EnableFieldList = True   'Excel 2002+
  .EnableFieldDialog = True
  .PivotCache.EnableRefresh = True
  For Each pf In pt.PivotFields
    .DragToPage = True
    .DragToRow = True
    .DragToColumn = True
    .DragToData = True
    .DragToHide = True
  Next pf
End With

End Sub

 

Dragging Enabled
Dragging Enabled

1. Pivot Tables -- Dynamic Data Source
2. Pivot Tables -- Data Field Layout
3. Pivot Tables -- Show and Hide Items
4. Pivot Tables -- Clear Old Items
5. Pivot Tables -- Field Settings  
6. Pivot Tables -- GetPivotData
7. Pivot Tables -- Grouping Data
8. Pivot Tables -- Multiple Consolidation Ranges
9. Pivot Tables -- Printing   
10. Pivot Tables -- Custom Calculations 
11. Pivot Tables -- Pivot Cache  
12. Pivot Tables -- Protection  
13. Pivot Tables -- Grand Totals

       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:53 PM