Control slicers by using VBA

Today’s author is Jan Karel Pieterse, an Excel MVP, who explains how to work with the SlicerCache object, and how customize buttons or change slicer properties by using VBA. For more information about slicers, read PivotTable Slicers on Jan Karel’s website: http://www.jkp-ads.com/.

Juggling the SlicerCaches collection

For each slicer you add to your workbook, Excel adds a SlicerCache object too, which controls which pivot table(s) your slicer controls.

So suppose we have three sheets, Sheet1, Sheet2 and Sheet3. Each sheet has a pivot table and all Pivot tables are based on one PivotCache. As soon as you add a slicer to each pivot table (even if the slicer ties to the same field of the Pivot table) you get three SlicerCache objects:

Hierarchy of the slicer and its family

The code below enumerates all slicer caches in your workbook:

Sub MultiplePivotSlicerCaches()
Dim oSlicer As Slicer
Dim oSlicercache As SlicerCache
Dim oPT As PivotTable
Dim oSh As Worksheet
For Each oSlicercache In ThisWorkbook.SlicerCaches
For Each oPT In oSlicercache.PivotTables
            oPT.Parent.Activate
            MsgBox oSlicercache.Name & "," & oPT.Parent.Name
Next
Next
End Sub

As soon as you check more than one pivot table on the PivotTable Connections dialog of a Slicer, the slicers in question will share a single SlicerCache object. The other SlicerCache object will be removed from the collection. This explains why you cannot go back by unchecking all but one pivot table in that dialog: all joined slicers will now be changed by changing the checked pivot table(s) on any of them. In turn, each checked pivot table becomes part of the PivotTables collection of the remaining SlicerCache object.

If you decide to select Slicer1 and change its pivot connections by checking both Pivottable1 and Pivottable2, one slicercache is deleted (the one belonging to the pivot table you checked to add to the current slicer). So the hierarchy changes to:

Changed hierarchy of slicers

So both Slicer1 and Slicer2 control pivot tables 1 and 2. The slicers 1 and 2 are in sync too because it is in fact the SlicerCache that is changed by the slicer. So the hierarchy in the picture above isn't entirely true.

Changing the buttons using VBA

It is easy enough to change the button appearance using a bit of VBA:

Sub AdjustSlicerButtonDimensions()
With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
'Note that changing the ColumnWidth also affects the Width of the slicer itself
        'So the next line will change the ColumnWidth!
        .Width = 300
End With
End Sub

Note that the numbers do not coincide with what is shown on the ribbon. Apparently the unit of measure differs between VBA and the ribbon.

Changing the slicer itself and some SlicerCache properties

Changing some aspects of your slicer using VBA isn't hard to do. In fact, the macro recorder makes finding out how this works relatively simple. After changing some settings and doing a bit of tidying up I got:

Sub AdjustSlicerSettings()
With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .Caption = "City"
        .DisplayHeader = True
        .Name = "City 2"
End With
With ActiveWorkbook.SlicerCaches("Slicer_City2")
        .CrossFilterType = xlSlicerNoCrossFilter
'xlSlicerCrossFilterShowItemsWithDataAtTop:
'   Visually indicate items with no data, items with data are pushed to the top
'xlSlicerCrossFilterShowItemsWithNoData:
'   Visually indicate items with no data, items with no data stay put
'xlSlicerNoCrossFilter:
'   No indication for items with no data.
        .SortItems = xlSlicerSortAscending
        .SortUsingCustomLists = False
        .ShowAllItems = False
'   This ensures that data no longer in the pivot cache is not shown on the slicer
End With
End Sub

Conclusion

Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find slicers a real gem in Excel 2010. A great addition to the product!

-- Jan Karel Pieterse