Not Displaying All within Page view of Pivot Tables

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Is there a way to not display All items within a page view
of a pivot table? I have a business case where I dont
want my users to have the ability to display the summation
of all items within a dimension, I only want them to be
able to click on one of the items below (Smokeless or
Smoking):

For Example:
All
Smokeless Tobacco
Smoking Tobacco

I have been playing around with this for a while and cant
come up with anything. If anyone knows if this is
possible, can you please pass some info along.

Thanks,

Jason
 
It's not foolproof, but you could disable the page selection, and add a
data validation dropdown with the items that you want users to select.

To disable page selection, you can use a macro:

Sub DisablePageSelection()
ActiveSheet.PivotTables(1).PageFields(1) _
.EnableItemSelection = False
End Sub

There are instructions here for a data validation dropdown:
http://www.contextures.com/xlDataVal01.html

To change the page field based on the selection in the data validation
list, add a Worksheet_Change event to the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$F$1" Then
ActiveSheet.PivotTables(1).PivotFields("Region") _
.CurrentPage = Target.Value
End If
Application.EnableEvents = True
End Sub
 
Back
Top