software

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 quot;Allquot;, as the users have to select
absolutely one item from the segment list.
(this default quot;Allquot; shows meaningless figures in the Pivot Table!)

I didn't see any option allowing me to hide this quot;allquot; 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 quot;Allquot; option hidden.

Your help will be appreciated.

PS: Version of Excel: 2003 SP1

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

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(quot;Regionquot;)

With pf
If .CurrentPage = quot;(All)quot; 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:
gt; I have done a report using a pivot table to show some results per segment.
gt; I have to put the Segment field in the Drop Page Field area.
gt;
gt; My problem is this list of segment contains sub-segments and Total Segments
gt; too, i.e. I don't want to have the option quot;Allquot;, as the users have to select
gt; absolutely one item from the segment list.
gt; (this default quot;Allquot; shows meaningless figures in the Pivot Table!)
gt;
gt; I didn't see any option allowing me to hide this quot;allquot; item in the drop list.
gt; I think I have seen once that it was possible using VBA code, but I can't
gt; find out how.
gt;
gt; Notes: This report has several Drop Page Fields, but not all of them need to
gt; have this quot;Allquot; option hidden.
gt;
gt; Your help will be appreciated.
gt;
gt; PS: Version of Excel: 2003 SP1--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html