Delete/Hide PivotTable PageFields "(ALL)" Option
 

Hi All,

I am using Excel 2000. I wish to get ride of that "(ALL)" option in
Excel PivotTable PageFields by delete/hide it using VBA.

The surprising thing is: This "(ALL)" not belong to PivotItem of
PageFields or PivotFields. It seems can't be accessed UNLESS it is
displayed as

PageFields.VisibleItem(1) (by default)

When I try

PageFields.PivotItem("(ALL)").Visible = False

I always get the same error message:

"Object doesn't support this property or method"

Thanks in advance.

Charles

Delete/Hide PivotTable PageFields "(ALL)" Option
 

As answered in microsoft.public.excel --

You could use the Worksheet_Calculate event to switch the selection to
the first item in the page field list. Place the following code on the
worksheet module:

'==========================
Private Sub Worksheet_Calculate()
'if (All) is selected from a page field
'the first item is selected instead
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables(1)
For Each pf In pt.PageFields
If pf.CurrentPage = "(All)" Then
pf.CurrentPage = pf.PivotItems(1).Name
End If
Next pf
Application.EnableEvents = True
End Sub
'===============================

Charles wrote:
Hi All,

I am using Excel 2000. I wish to get ride of that "(ALL)" option in
Excel PivotTable PageFields by delete/hide it using VBA.

The surprising thing is: This "(ALL)" not belong to PivotItem of
PageFields or PivotFields. It seems can't be accessed UNLESS it is
displayed as

PageFields.VisibleItem(1) (by default)

When I try

PageFields.PivotItem("(ALL)").Visible = False

I always get the same error message:

"Object doesn't support this property or method"

Thanks in advance.

Charles


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html