Skip to main content
Skip to main content
Microsoft 365

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:

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
            MsgBox oSlicercache.Name & “,” & oPT.Parent.Name
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
‘   Visually indicate items with no data, items with data are pushed to the top
‘   Visually indicate items with no data, items with no data stay put
‘   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


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

You may also like these articles

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365
• 5 min read

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards
• 5 min read

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting
• 6 min read

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…