Search Contextures Sites

 

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
    With pf
      .DragToPage = True
      .DragToRow = True
      .DragToColumn = True
      .DragToData = True
      .DragToHide = True
    End With
  Next pf
End With

End Sub

 

Dragging Enabled
Dragging Enabled

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

Learn how to create Excel dashboards.

 
       Home     Excel Tips     Excel Files      Blog    Contact

RSS Feed

Privacy Policy

 

 

The Excel Store

 

Last updated: June 11, 2009 9:12 PM