One Slicer To Control Them All

In Excel 2007, Microsoft added structured tables. Although far from being my favourite Excel feature, they are certainly a step in the right direction, and Microsoft are using them more and more in other functions.

Excel 2010 saw the addition of slicers with pivot tables, a feature that provides a much more intuitive and visual filtering of pivot tables.

Excel 2013 gave us the ability to add a slicer to a structured table (see the step-wise progress here :D?), definitely a welcome improvement. One of the great features of slicers with pivot tables is that you can link the slicer to multiple pivots, synchronised filtering. Unfortunately, in their wisdom Microsoft did not see fit to provide similar functionality to slicers attached to structured tables.

The question as to whether you can control multiple tables from one slicer, and the answer is invariably no. Recently this question was asked on LinkedIn, so I built such a facility, One Slicer To Control Them All.

Slicers cannot control multiple tables, so my approach is to create a pivot table from one of the tables, designated as the master table in this context, and add slicer(s) for this pivot. Selecting from the slicer will ripple down to the pivot table, and changes to this table can be trapped using the PivotTableUpdate event. By trapping the changes in this event, we can then filter all tables for this criteria.

That is basically all that is required, but to cater for any changes in the master table, changes that wouldn’t automatically reflect any slicer selections, I trap those changes using the SelectionChange event and refresh the pivot (which will then trigger the PivotTableUpdate event, thereby filtering the new data accordingly). I am only trapping the master table, it would not be difficult to trap a change to any of the tables.

One other thing it caters for is the situation where one or more of the tables has headers suppressed (why you would do this is beyond me, but there is the option in the Table Style Options group of the Table context ribbon), in which case when testing the HeaderRowRange, which is done to find which column to filter, nothing is returned, so nothing gets filtered. This was pointed out to me by Zack  Barresse, an Excel MVP who wrote the book on structured tables, and he also provided me with a function to resolve this. This function, HeaderRowlabels in the code below, returns an array of column headings regardless of this header setting. By checking the slicer caption against this array, I can get the column regardless. I also added a check in my code to test whether headers are shown, as the filter restores the headings when suppressed, so I need to reset to the user’s choice.

Outlining the steps in a little more detail:

Setup

First, I create a pivot table from the table that is designated as the master table, and I display all of the items to that pivot (you can hide it far off to the right, or on a hidden sheet, if you don’t want it to be shown).

I then add slicer(s) to the pivot table for my filter fields. The VBA traps the Worksheet_PivotTableUpdate event, and iterates all tables to filter by the slicer criteria.

The names of the master table and the controlling pivot table are setup as module constants to facilitate maintenance.

It is possible that the user has table headers turned off, in which case .HeaderRowRange returns nothing, the function HeaderRowLabels gets an array of header labels regardless of this setting.

This code assumes that all of the tables are on the same sheet, if they are spread across multiple sheets the code will need to be amended to cater for this.

Managing Table Additions/Changes

As users will be adding to the tables, and as the pivot does not automatically update, it needs to be refreshed which is managed from code.

The Worksheet_SelectionChange code checks if any cell that is changed is within the master table, the table that the pivot is generated from, it refreshes the pivot table, a refresh that does ripple through to the slicers.

This could easily be changed to check all tables.

Limitations

This technique has clear limitations, it is almost entirely predicated upon the assumption that the tables are of the same structure. The slicers do not get updated if any of the tables other than the master table is updated.

Within those constraints it seems to work well, and some of the constraints can be mitigated with code changes.

Code

Option Explicit

Const TABLE_MASTER As String = "tblMaster"
Const PIVOT_NAME As String = "pvtControl"

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim Slicer As SlicerItem
Dim Table As ListObject
Dim Headers As Boolean
Dim values As Variant
Dim nextrow As Long
Dim idx As Long
Dim i As Long
    For i = 1 To Target.Slicers.Count
    
        nextrow = 1
        ReDim values(1 To nextrow)
        
        For Each Slicer In Target.Slicers(i).SlicerCache.VisibleSlicerItems
    
            ReDim Preserve values(1 To nextrow)
            values(nextrow) = Slicer.Name
            nextrow = nextrow + 1
        Next Slicer
        
        For Each Table In Me.ListObjects
        
            Headers = Table.ShowHeaders
            idx = Application.Match(Target.Slicers(i).Caption, HeaderRowLabels(Table), 0)
            If nextrow > Target.Slicers(i).SlicerCache.SlicerItems.Count Then
            
                Table.Range.AutoFilter Field:=idx
            Else
            
                Table.Range.AutoFilter Field:=idx, Criteria1:=values, Operator:=xlFilterValues
            End If

            Table.ShowHeaders = Headers
        Next Table
    Next i
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tableCell As Boolean

    On Error Resume Next
    tableCell = Target.ListObject.Name = TABLE_MASTER
    On Error GoTo 0
    If tableCell Then Me.PivotTables(PIVOT_NAME).PivotCache.Refresh
End Sub


Function HeaderRowLabels(ByVal Table As ListObject) As Variant
Dim TableColumn As ListColumn
Dim TableHeaders() As Variant
    
    If Table.ShowHeaders Then
    
        ReDim TableHeaders(1 To Table.ListColumns.Count)
        If UBound(TableHeaders) = 1 Then
        
            TableHeaders(1) = Table.HeaderRowRange.Value
        Else
        
            TableHeaders = Table.HeaderRowRange.Value
        End If
        
        HeaderRowLabels = TableHeaders
    Else
    
        ReDim TableHeaders(1 To Table.ListColumns.Count)
        For Each TableColumn In Table.ListColumns
        
            TableHeaders(TableColumn.Index) = TableColumn.Name
        Next TableColumn
        
        HeaderRowLabels = TableHeaders()
    End If
End Function
Advertisements
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s