Which Is Not True Of Filters In A Pivottable?

In some Excel pivot tables, yous might want to prevent selection of the "(All)" option in a Report Filter. At that place's no built-in option to exercise that. Instead, yous can use this macro to stop people from choosing the ALL option.

Why Prevent Selecting ALL?

Why would you lot desire to prevent people from selecting the (All) option in a pivot tabular array filter?

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

If a unmarried appointment is selected, the formulas work correctly, and show the full amounts for the selected date.
If (All) is selected, the formulas prove a message — "Select single society appointment".

vbablockall08

Ii Types of Pivot Tables

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

Normal Pin Tabular array – not in the Data Model
OLAP-based Pivot Table – in the Information Model

The pin tables expect similar, merely in that location are differences in the drop down list of items.

Here is OrderDate drop downward list for the normal pivot table

vbablockall09

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

vbablockall10

PivotTable Field Lists

There are differences in the PivotTable Field Lists too.

Here is the field list for the normal pivot table. Information technology lists each field from the source data, and at that place'southward a More Tables command at the bottom of the list.

vbablockall11

If yous click the More Tables control, a bulletin appears, asking if you desire to create a new pivot tabular array, using the Data Model.

vbablockall13

OLAP-Based Pin Tabular array Field List

Here'south the field list for the OLAP-based pin table in this instance.

vbablockall12

It shows the source tabular array at the superlative, with the fields in a hierarchy below that.

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

Macro to Stop (All) Option

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

The lawmaking 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 up as the Target.

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

          If Target.PivotCache.OLAP          Then

So, the applicable section of code runs

If information technology is OLAP-based, a department with OLAP-based backdrop runs
It it isn't, a section with normal pivot table backdrop runs.

Macro Lawmaking on Sheet Module

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

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

Individual Sub Worksheet_PivotTableUpdate _ 	(ByVal Target Equally PivotTable) Dim pf As PivotField Dim strPg As Cord Dim strAll As String On Error GoTo exit_Handler  Application.EnableEvents = Fake Application.ScreenUpdating = False  If ActiveSheet.Name = Me.Proper noun Then     For Each pf In Target.PageFields       If Target.PivotCache.OLAP And then         strPg = Correct(pf.CurrentPageName, 5)         strAll = "[All]"       Else         strPg = pf.CurrentPage         strAll = "(All)"       End If          If strPg = strAll So           Awarding.Undo           MsgBox "Please select a single item."       End If     Next pf End If  exit_Handler:     Set pf = Cipher     Awarding.EnableEvents = True     Awarding.ScreenUpdating = True Cease Sub

Get the Sample Workbook

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

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

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

_______________________

Prevent Selection of ALL in Pivot Table Report Filter

_______________________