Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	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 Sub   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 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 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 Sub NOTE: 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 Sub Download the Sample File Download the sample pivot table tutorial file

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

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

 

 

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