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:
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 IfEnd 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) NextEnd 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.
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