PIVOT TABLE ALL option

  • Thread starter Thread starter Aman
  • Start date Start date
A

Aman

Is there any way to remove the "All" option that comes in the page
fields in a pivot table??
 
As Jim said, you can't remove the "All" option in the page field. With
programming, you could select another item if the user selects "All".

For example, the following 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.

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

With pf
If .CurrentPage = "(All)" Then
i = 1
For i = 1 To pf.PivotItems.Count + 1
On Error Resume Next
.CurrentPage = .PivotItems(i).Name
If Err.Number = 0 Then
Exit For
End If
Next i
MsgBox "The (All) option is not available"
End If
End With

End Sub
'============================
 
Back
Top