Not Displaying All within Page view of Pivot Tables

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
 
D

Debra Dalgleish

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
 
Top