Don't want the "All" option in the Drop Page Field of a pivot tabl
 

I have done a report using a pivot table to show some results per segment.
I have to put the Segment field in the Drop Page Field area.

My problem is this list of segment contains sub-segments and Total Segments
too, i.e. I don't want to have the option "All", as the users have to select
absolutely one item from the segment list.
(this default "All" shows meaningless figures in the Pivot Table!)

I didn't see any option allowing me to hide this "all" item in the drop list.
I think I have seen once that it was possible using VBA code, but I can't
find out how.

Notes: This report has several Drop Page Fields, but not all of them need to
have this "All" option hidden.

Your help will be appreciated.

PS: Version of Excel: 2003 SP1

Don't want the "All" option in the Drop Page Field of a pivottabl
 

You can't suppress the "(All)" option in the page field. With
programming, you could select another item if the user selects "All".

For example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
Dim pf As PivotField
Set pt = Me.PivotTables(1)
Set pf = pt.PivotFields("Region")

With pf
If .CurrentPage = "(All)" Then
.CurrentPage = .PivotItems(1).Name
End If
End With
End Sub
'============================

This code is stored on the worksheet's code module --
Right-click the sheet tab, and choose View Code
Paste the code where the cursor is flashing.


Vincerix wrote:
I have done a report using a pivot table to show some results per segment.
I have to put the Segment field in the Drop Page Field area.

My problem is this list of segment contains sub-segments and Total Segments
too, i.e. I don't want to have the option "All", as the users have to select
absolutely one item from the segment list.
(this default "All" shows meaningless figures in the Pivot Table!)

I didn't see any option allowing me to hide this "all" item in the drop list.
I think I have seen once that it was possible using VBA code, but I can't
find out how.

Notes: This report has several Drop Page Fields, but not all of them need to
have this "All" option hidden.

Your help will be appreciated.

PS: Version of Excel: 2003 SP1


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html