Prevent Selection of ALL in Pivot Table Report Filter

In some Excel pivot tables, you might want to prevent selection of the “(All)” option in a Report Filter. There’s no built-in option to do that. Instead, you can use this macro to stop people from choosing the ALL option.

Why Prevent Selecting ALL?

Why would you want to prevent people from selecting the (All) option in a pivot table filter?

In this example, the OrderDates sheet has GetPivotData formulas that refer to the OrderDate selection cell in the Report Filter area.

If a single date is selected, the formulas work correctly, and show the total amounts for the selected date.
If (All) is selected, the formulas show a message — “Select single order date”.

Two Types of Pivot Tables

In this example, there are two types of pivot tables on the OrderDates sheet.

Normal Pivot Table – not in the Data Model
OLAP-based Pivot Table – in the Data Model

The pivot tables look similar, but there are differences in the drop down list of items.

Here is OrderDate drop down list for the normal pivot table

And here is the drop down list for the OLAP-based pivot table. All of the items have a plus sign at the left.

PivotTable Field Lists

There are differences in the PivotTable Field Lists too.

Here is the field list for the normal pivot table. It lists each field from the source data, and there’s a More Tables command at the bottom of the list.

If you click the More Tables command, a message appears, asking if you want to create a new pivot table, using the Data Model.

OLAP-Based Pivot Table Field List

Here’s the field list for the OLAP-based pivot table in this example.

It shows the source table at the top, with the fields in a hierarchy below that.

When you point to the table, a screen tip shows the data source name and location, and the name of the table in the Data Model.

Macro to Stop (All) Selection

Because the pivot tables have a different structure, the code that stops people from selecting (All) needs to have two sections.

The code in the sample file runs when either of the pivot tables on the OrderDates sheet is updated.

In the code, the updated pivot table is set as the Target.

First, the code checks if the pivot cache for that Target is OLAP-based

     If Target.PivotCache.OLAP Then

Then, the applicable section of code runs

If it is OLAP-based, a section with OLAP-based properties runs
It it isn’t, a section with normal pivot table properties runs.

Macro Code on Sheet Module

The following code is on the OrderDates code module, and prevents selection of (All) for both types of pivot table.

You can copy this code to your own Excel file, and paste it onto the sheet module where your pivot tables are located.

Private Sub Worksheet_PivotTableUpdate _
	(ByVal Target As PivotTable)
Dim pf As PivotField
Dim strPg As String
Dim strAll As String
On Error GoTo exit_Handler

Application.EnableEvents = False
Application.ScreenUpdating = False

If ActiveSheet.Name = Me.Name Then
    For Each pf In Target.PageFields
      If Target.PivotCache.OLAP Then
        strPg = Right(pf.CurrentPageName, 5)
        strAll = "[All]"
      Else
        strPg = pf.CurrentPage
        strAll = "(All)"
      End If
  
      If strPg = strAll Then
          Application.Undo
          MsgBox "Please select a single item."
      End If
    Next pf
End If

exit_Handler:
    Set pf = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Get the Sample Workbook

To get the sample Excel file, with the pivot tables and worksheet module code, go to the Pivot Table Report Filter Macros page on my Contextures website.

In the download section, look for the workbook – Block (All) – OLAP or Normal Pivot Tables

The zipped file is in xlsm format, and contains the macro shown above.

_______________________