Home Newsletter

Nederlandse pagina niet beschikbaarHome > Article index > Pivottable Slicers >

Slicers and VBA

Pages in this article

  1. Why Slicers
  2. Configuring Slicers
  3. Slicers and VBA
  4. Synchronising Slicers
  5. Show Selected Items

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 168 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (18-10-2018 16:49:52) deeplink to this comment

Hi Tad,

You're welcome!


Comment by: Andreas Katits (24-1-2019 10:22:05) deeplink to this comment

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) deeplink to this comment

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) deeplink to this comment

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.


Comment by: Cam (1-10-2021 12:17:00) deeplink to this comment

Hi Jan and everyone.

I have an issue with my slicers. Basically I have a dashboard with 5 graphs coming from 5 Pivot Tables. 4 of them come from the same base table, but one comes from a different one. I don't think it's possible to combine them. Both tables have a dataset in common: area code. I would like that when I select a number in my slicer 1, the slicer 2 selects the same one automatically.

How would that look in a VBA code ?

Thank you !!!


Comment by: Jan Karel Pieterse (1-10-2021 13:30:00) deeplink to this comment

Hi Cam,

I'd say "it depends". If you use the data model for the pivot tables, you can add an in-between table which contains all unique area codes. If you then set the correct relationships between the tables you should be able to use the new area code in both your pivot tables and slicer to filter then with one slicer. Clear as mud?


Comment by: Peter Roberts (15-1-2022 09:06:00) deeplink to this comment

If I have a table of accounting transactions for 12 months data in a pivot table
Is it possible to create a slicer that displays a column with the total for month 3 and a second column with the total year to date [ie ytd]


Comment by: Jan Karel Pieterse (17-1-2022 11:44:00) deeplink to this comment

Hi Peter,

Slicers can only contain the items from an existing field (column) of the source data of your pivot table. Displaying (sub)totals in a slicer is not possible.


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.






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