Search Contextures Sites

Excel Pivot Table Tutorial -- Protection

  1. Disable Selection
  2. Enable Selection
  3. Restrict PivotTable
  4. Allow PivotTable
  5. Pivot Table Tutorial List
Download the sample file for this pivot table tutorial
     

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()
'pivot table tutorial by contextures.com
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()
'pivot table tutorial by contextures.com
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()
'pivot table tutorial by contextures.com
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()
'pivot table tutorial by contextures.com
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
    With pf
      .DragToPage = True
      .DragToRow = True
      .DragToColumn = True
      .DragToData = True
      .DragToHide = True
    End With
  Next pf
End With

End Sub   

Download the sample file for this pivot table tutorial

 

Dragging Enabled
Dragging Enabled

   

Pivot Table Tutorial List

Excel Pivot Table -- Introduction 
Excel Pivot Table -- Create a Pivot Table in Excel 2007 
Excel Pivot Table -- Data Field Layout
Excel Pivot Table -- Show and Hide Items
Excel Pivot Table -- Clear Old Items
Excel Pivot Table -- Field Settings
Excel Pivot Table -- GetPivotData
Excel Pivot Table -- Grouping Data
Excel Pivot Table -- Multiple Consolidation Ranges
Excel Pivot Table -- Printing   
Excel Pivot Table -- Custom Calculations 
Excel Pivot Table -- Pivot Cache     
Excel Pivot Table -- Protection  
Excel Pivot Table -- Grand Totals
Excel Pivot Table -- Running Totals  
Excel Pivot Table -- Filter Source Data  

Pivot Table Blog -- Related Posts

Refreshing a Pivot Table on a Protected Sheet

Learn how to create Excel dashboards.

 

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.

 

Last updated: February 6, 2010 1:45 AM