Hello,
I'm trying to figure out how to get rid of the "All" option which is always populated in a pivot table drop down menu in Excel 2003. If the users of my pivot table select "All" the numbers that they will view will not have any meaning. I tried using the below VBA code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim PF As PivotField
For Each PF In Target.PivotFields
If PF.Orientation = xlPageField Then
If PF.CurrentPage = "(All)" Then
PF.CurrentPage = PF.PivotItems(1).Name
End If
End If
Next PF
End Sub
But it doesn't work and I keep getting the error message "Unable to set the _Default property of the PivotItem class"
Thanks!!
I'm trying to figure out how to get rid of the "All" option which is always populated in a pivot table drop down menu in Excel 2003. If the users of my pivot table select "All" the numbers that they will view will not have any meaning. I tried using the below VBA code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim PF As PivotField
For Each PF In Target.PivotFields
If PF.Orientation = xlPageField Then
If PF.CurrentPage = "(All)" Then
PF.CurrentPage = PF.PivotItems(1).Name
End If
End If
Next PF
End Sub
But it doesn't work and I keep getting the error message "Unable to set the _Default property of the PivotItem class"
Thanks!!