Last updated: February 14, 2013 2:33 PM
Search Contextures Sites ![]()
![]()
![]()
Excel Pivot Table Protection
- Disable Selection
- Enable Selection
- Restrict PivotTable
- Allow PivotTable
- Refresh Pivot Table Protected Sheet
- Refresh Pivot Table Connected to Protected Sheet
- Download the Sample File
- Pivot Table Tutorial List
Download the pivot table tutorial 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() '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![]()
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 SubRestrict 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 SubAllow 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![]()
Refresh Pivot Table on Protected Sheet
When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables.
However, once the sheet is protected, you can’t create a new pivot table. Also, you won't be able to refresh a pivot table on the worksheet, because that feature is disabled on a protected sheet. If you right-click in a pivot table cell, the Refresh command is disabled.
You could manually unprotect the worksheet, refresh the pivot table, and then protect the sheet again.
Another option, if you're trying to refresh the pivot table programmatically, is to unprotect the worksheet, refresh the pivot table, and then protect the sheet again.
Sub UnprotectRefresh() On Error Resume Next With Activesheet .Unprotect Password:="mypassword" .PivotTables(1).RefreshTable .Protect Password:="mypassword", _ AllowUsingPivotTables:=True End With End SubNOTE: If other pivot tables are connected to the same pivot cache are on protected sheets, you can use the code below, to unprotect all the sheets.
Refresh Pivot Table Connected to Protected Sheet
If you try to refresh a pivot table, you might see a message that says: "That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data."
This occurs if the pivot table that you're trying to refresh shares the same pivot cache as another pivot table, and that pivot table is on a protected sheet.
As the message says, you could manually unprotect the other sheet, and then refresh the pivot table.
Another option, if you're trying to refresh the pivot table programmatically, is to unprotect all the sheets, refresh the pivot tables, and then protect the sheets again.
Sub UnprotectRefreshAll() Dim ws As Worksheet On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Password:="mypassword" Next ws ActiveWorkbook.RefreshAll For Each ws In ActiveWorkbook.Worksheets ws.Protect Password:="mypassword", _ AllowUsingPivotTables:=True Next ws End SubDownload the Sample File
Download the sample pivot table tutorial file
Contextures Inc., Copyright ©2013
All rights reserved.