You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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/.
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.
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 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
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
Hi Diego, I ran across the work you guys have been doing connecting Excel to Windows HPC. We're looking to connect with you about GPU-accelerating Excel, via our new product libJacket (http://www.accelereyes.com). Would be great to chat with you by phone if possible. Email me at email@example.com. Thanks!
I messed about with this code, and tried to use it to change a slicer that I have created on a pivot that is using a Cube a a source, I wanted to set up the slicer so that users wouldn't have to change it for a value that changes every time the cube builds.
the problem is that it won't let me change the selected property of items in the slicer, I just get an error 1004 'Application-defined or Object-defined error'
I guess this means that we can't change these values for pivots that are created out of extenal data, which from my point of view makes the whole thing useless!