Compartilhar via

Using Cell Text to Filter PivotTables

  • Artigo

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:

  1. Detect when the user has entered a value into the cell;
  2. 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

Comments

  • Anonymous
    February 04, 2008
    Why can't you display code as text? The picture is truncated in the display, and it can't be pasted into a module.

  • Anonymous
    February 04, 2008
    Hi Jon, Sorry about that ... I've changed the code snippets to text.

  • Anonymous
    February 05, 2008
    The comment has been removed

  • Anonymous
    February 05, 2008
    Gabhan - Thanks for providing the code as text. Such a simple change, such an increase in usability.

  • Anonymous
    February 07, 2008
    Hi Colin, There's a recent article over on the Excel team blog about custom calculations in PivotTables. It doesn't cover all your points but it may be of some use to you. http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx As for external references, by that do you mean using cell references in calculations (such as Sales Avg * A$1$)? or do you mean something outside of Excel?

  • Anonymous
    February 07, 2008
    "There's a recent article over on the Excel team blog about custom calculations in PivotTables. It doesn't cover all your points but it may be of some use to you." Yes, I read that article.  I found it very useful but it addresses issues specific to PTs created from OLAP sources.  I'm thinking about non-OLAP sources in this instance (e.g. PTs created from relational data sources) "As for external references, by that do you mean using cell references in calculations (such as Sales Avg * A$1$)? or do you mean something outside of Excel?" The former (external to the PT but within the same workbook).  One other thing I forgot to mention.  Another reason I've had to write code outside the pivot table is for  calculating running totals that run over multiple years.  A typical scenario would be to keep running totals of individual shipped product quantities over months and years. In the case of the PT running totals custom calculation, the best that you can do is use month as the base field but the running totals are reset at the beginning of each year.  That's the nature of the beast.   Colin

  • Anonymous
    April 02, 2008
    Can't get code to work in Excel 2003.  What am I missing? Thx.

  • Anonymous
    May 14, 2008
    may I ask a question?  I need to use pivot table to generate 12 month rolling data, is it possible to: every time, we open the file, the pivot table with use date ( one year ago)  -- today as the filture range to do so?  for example, 2008/05/14 is : 33896,  how to add the date into the pivot table to generate automatically new data each day??? thanks

  • Anonymous
    May 22, 2008
    The comment has been removed

  • Anonymous
    September 26, 2008
    Have you been able to modify the code for OLAP pivot tables?  If so, can you please post it.  Thanks.