Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Pivottable Slicers > Slicers and VBA

Slicers and VBA

This page has also been published on The Microsoft Office Blog.

Juggling the SlicerCaches collection

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

So suppose we have a workbook with two Pivot caches. Pivotchache1 has two pivot tables: Pivottable1 and Pivottable2. As soon as you add a slicer to a Pivottable (even if the slicer ties to the same field of the Pivottable) you get a SlicerCache object tied to the pivotCache to which the pivot table belongs:

Hierarchy of the slicer and it's family
Hierarchy of the slicer and its family

Note that the above picture is not entirely correct. In the object model, PivotTables are in fact tied to a slicerCache, not to a slicer. But on the userinterface side of things it is the slicer the user actually sees, which is why I put that in-between.

The code below enumerates all slicer caches and their associated pivottables 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 Pivottable 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 Pivottable in that dialog: all joined slicers will now be changed by changing the checked Pivottable(s) on any of them. In turn, each checked Pivottable 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 beloning to the Pivottable you checked to add to the current slicer). So the hierarchy changes to:

Changed hierarchy of slicers
Changed hierarchy of slicers

So both Slicer1 and Slicer2 control Pivottables 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

Next: synchronising pivottables


 


Comments

Showing last 8 comments of 164 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (21-10-2016 14:36:43)

Hi Mads,

For startes, you might try replacing this:

    For Each oSi2 In oSL.SlicerItems
        oSi2.Selected = True
    Next


with:

oSl.ClearManualFilter
.

 


Comment by: Radich (31-1-2018 15:18:49)

HI !

im new in this and i trying to select just 1 parameter in my slice, i use .clearmanualfilter and then

With ActiveWorkbook.SlicerCaches("VENDOR_ID")
        .SlicerItems("0177797197").Selected = True
        For Each slcMiSlicer In ActiveWorkbook.SlicerCaches("VENDOR_ID").SlicerItems
         If Not slcMiSlicer.Caption = strActivar Then
         If (slcMiSlicer.Selected <> True) Then
         slcMiSlicer.Selected = False
         End If
         End If
    Next slcMiSlicer
End With

the problem Vendor_ID hace like 15000 so its take a lot of time

its any other way to clear all and just select 1 faster ? without if.

thanks

 


Comment by: Jan Karel Pieterse (31-1-2018 16:33:39)

One thing you can try is adding another pivottable (which you add to the slicer as well!) with just one page field (VENDOR_ID) and then have the sync code write that single value to the cell of the page field directly.

 


Comment by: Tad Hammer (18-10-2018 16:20:37)

Jan Karel Pieterse-
Thank you so much for all this information!!!
I found the answer to a question I had about how to access a slicer item when you are connected to a cube. I had been searching and searching. Thank you so much!!!
Best regards,
Tad Hammer

 


Comment by: Jan Karel Pieterse (18-10-2018 16:49:52)

Hi Tad,

You're welcome!

 


Comment by: Andreas Katits (24-1-2019 10:22:05)

Hi,
very nice article, but I miss one thing I want to share:

If you want to find slicers only used on a specific sheet you can do the following to access the SlicerCache based on PivotTables


Sheets("Your Sheet").Pivottables(1).slicers("SlicerName or Index").slicercache


Best regards,
Andreas

 


Comment by: Karan (3-12-2019 14:23:00)

Hello Jan,

Is there any way I can select only specific items in a slicer? For example, let's say that there is a slicer for "City" and I only want to select cities with a population of more than 500 (assume the population is also part of the data). Can I write a VBA code to select just those cities?

 


Comment by: J.K. Pieterse (11-12-2019 11:43:00)

Hi Karan,

Apologies for the belated reply.
A slicerItem in a slicer object has a HasData property which is false if that item of the slicer would cause the pivottable to be empty when selected. To use that information, you must first filter your pivottable on the "population more than 500" condition.

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.