how to remove "(All)" in a pivot table using VBA

hi everyone!
does anyone know if it is possible to remove the "(All)"
or "(Show All)" from a pivot table.

here's a piece of my code for setting up the first
element of each pagefeild items, but I would also want to
remove the field "(All)"

With ActiveWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
.Connection = strConnection
.CommandType = xlCmdSql
.CommandText = "exec
fc_GetViewAsDenormalizedTable @View_id=" & intViewID & ",
@tableset=" & intTableset
.CreatePivotTable
TableDestination:=ActiveSheet.Range("A3"),
TableName:="PivotTable1",
DefaultVersion:=xlPivotTableVersion10
End With

Set pt = ActiveSheet.Range("A3").PivotTable

For Each ptField In pt.PivotFields
If LCase(ptField.Name) <> "data_id" And LCase
(ptField.Name) <> "usertable_login" And LCase
(ptField.Name) <> "view_id" And LCase(ptField.Name)
<> "transfert_date" And LCase(ptField.Name)
<> "control_code" Then
Select Case LCase(ptField.Name)
Case "cou"
ptField.Orientation = xlRowField
Case "country"
ptField.Orientation = xlRowField
Case "dcountry"
ptField.Orientation = xlRowField
Case "yea"
ptField.Orientation = xlColumnField
Case "year"
ptField.Orientation = xlColumnField
ptField.ShowAllItems = False
Case "data_value"
pt.AddDataField ptField
Case Else
ptField.Orientation = xlPageField
strFirstMember = ptField.PivotItems(1)
ptField.CurrentPage = strFirstMember
End Select
End If
Next ptField

pt.RowGrand = False
pt.ColumnGrand = False
pt.NullString = ".."

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("Rep")

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.


[email protected] wrote:
> hi everyone!
> does anyone know if it is possible to remove the "(All)"
> or "(Show All)" from a pivot table.
>
> here's a piece of my code for setting up the first
> element of each pagefeild items, but I would also want to
> remove the field "(All)"
>
> With ActiveWorkbook.PivotCaches.Add
> (SourceType:=xlExternal)
> .Connection = strConnection
> .CommandType = xlCmdSql
> .CommandText = "exec
> fc_GetViewAsDenormalizedTable @View_id=" & intViewID & ",
> @tableset=" & intTableset
> .CreatePivotTable
> TableDestination:=ActiveSheet.Range("A3"),
> TableName:="PivotTable1",
> DefaultVersion:=xlPivotTableVersion10
> End With
>
> Set pt = ActiveSheet.Range("A3").PivotTable
>
> For Each ptField In pt.PivotFields
> If LCase(ptField.Name) <> "data_id" And LCase
> (ptField.Name) <> "usertable_login" And LCase
> (ptField.Name) <> "view_id" And LCase(ptField.Name)
> <> "transfert_date" And LCase(ptField.Name)
> <> "control_code" Then
> Select Case LCase(ptField.Name)
> Case "cou"
> ptField.Orientation = xlRowField
> Case "country"
> ptField.Orientation = xlRowField
> Case "dcountry"
> ptField.Orientation = xlRowField
> Case "yea"
> ptField.Orientation = xlColumnField
> Case "year"
> ptField.Orientation = xlColumnField
> ptField.ShowAllItems = False
> Case "data_value"
> pt.AddDataField ptField
> Case Else
> ptField.Orientation = xlPageField
> strFirstMember = ptField.PivotItems(1)
> ptField.CurrentPage = strFirstMember
> End Select
> End If
> Next ptField
>
> pt.RowGrand = False
> pt.ColumnGrand = False
> pt.NullString = ".."


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