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.


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