Using Cell Text to Filter PivotTables

Article
01/31/2008

Sometimes it would be useful to be able to use cells to select items in a PivotTable. For example, let's say we have a PivotTable containing a field called Region. Instead of using the filter dropdown to select the region from a list it would be nice if we could use a cell to specify the region. That way, the user could just type a region, such as Europe, into a cell and the PivotTable would only show data for Europe.

In this post, I'll present some VBA code that does just this.

Let's take an example.

We have a PivotTable based on some census data in our workbook. We have Occupation on rows and Education Level on columns and our measure is average age. So we are looking at the average ages of different occupations and education levels.

We then want to filter this data by region. So we add the Region field to report filters. Now we can see the different values for different regions by changing the in-built filter dropdown.

But let's say that, instead of using the filter dropdown, we want the PivotTable to get the region selection from a specific cell. In other words, we want the user to be able to type Europe into a cell and the PivotTable updates to show the value for Europe, just as if the user had used the filter dropdown to select Europe.

To do this, we need to do two things:

Detect when the user has entered a value into the cell;
Update the Region PivotField object with the value of the cell;

For the sake of clarity, let's assign a name to the cell where the user will enter the new selection for the field (i.e. let's make the cell a named range). Since we're changing the Region field, let's give the cell a name of RegionFilterRange.

To detect when RegionFilterRange has changed, we handle the Workbook.SheetChange event. This event passes in (as a parameter) a Range object which represents the changed range. If this range intersects with the RegionFilterRange then we know that RegionFilterRange has changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Application.Range(RegionRangeName)) _
Is Nothing Then
UpdatePivotFieldFromRange _
RegionRangeName, PivotFieldName, PivotTableName
End If
End Sub

PivotFieldName is the name of the the field we are updating (in this case Region) and PivotTableName is the name of the PivotTable we are changing.

The code for updating the PivotField object is implemented in the function UpdatePivotFieldFromRange. I've made the code generic so that it can update any field in any PivotTable (in the active workbook) to the value of a specified range (these three things are passed in as parameters).

Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _
PivotTableName As String)

    Dim rng As Range
    Set rng = Application.Range(RangeName)

    Dim pt As PivotTable
    Dim Sheet As Worksheet
    For Each Sheet In Application.ActiveWorkbook.Worksheets
        On Error Resume Next
        Set pt = Sheet.PivotTables(PivotTableName)
    Next
    If pt Is Nothing Then GoTo Ex
    On Error GoTo Ex

    pt.ManualUpdate = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Dim Field As PivotField
    Set Field = pt.PivotFields(FieldName)
    Field.ClearAllFilters
    Field.EnableItemSelection = False
    SelectPivotItem Field, rng.Text
    pt.RefreshTable

Ex:
    pt.ManualUpdate = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Public Sub SelectPivotItem(Field As PivotField, ItemName As String)
Dim Item As PivotItem
For Each Item In Field.PivotItems
Item.Visible = (Item.Caption = ItemName)
Next
End Sub

UpdatePivotFieldFromRange first of all gets a reference to the required PivotTable and stores it in pt. Then it gets a reference to the required PivotField and stores that in Field.

pt.ManualUpdate = True stops the PivotTable from automatically updating as we make changes. It basically means that we have to tell the PivotTable when it should update. Application.EnableEvents = False stops events like Workbook.SheetChange from firing while our code is executing.

SelectPivotItem loops through all the items in the field and sets each one's Visible property to False except for the item specified by ItemName which is set to True. This ensures that only ItemName will be visible in the field.

Notice that in UpdatePivotFieldFromRange we set Field.EnableItemSelection = False. This hides the dropdown filter window from the user so they can't click on the PivotTable and change the field selection using the filter dropdown list.

So, our code now enables us to enter an item name in the RegionFilterRange named range and use that value to filter our PivotTable.

It doesn't matter where the Region field is. It could be on Report Filters, Columns or Rows. In the following screenshot, we have moved Region to Columns.

Summary

Cells can be used to filter PivotTables by programmatically setting the field selection to the value of the cell;
Use the Workbook.SheetChange event to detect changes to the cell;
Set Application.EnableEvents = False and PivotTable.ManualUpdate = True to avoid Excel events from firing and the PivotTable automatically updating itself while your code exeutes;

I have only talked about PivotTables that are based on data inside Excel. This code won't work for OLAP PivotTables. I might post a OLAP version in a later article if there is interest.

Download the Source Code

An example Excel 2007 workbook containing the VBA, data and PivotTable for this post has been published to the MSDN Code Gallery and can be downloaded by clicking on the link below (go to the Releases tab to download the workbook).

Click here to download

Using Cell Text to Filter PivotTables

Additional resources