Search Contextures Sites

Excel Pivot Table Tutorial -- Protection

Disable Selection Enable Selection Restrict PivotTable Allow PivotTable Pivot Table Tutorial List Download the pivot table tutorial sample file	Amazon.com

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

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

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

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 pivot table tutorial file

Dragging Enabled

 

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2012 All rights reserved.	Debra Dalgleish