Slicers control using VBA

Author of today is Jan Karel Pieterse, an Excel MVP, which explains how to use the SlicerCache object and how to customize the buttons or change the properties of the slicer using VBA. For more information about slicing machines, read the PivotTable slicers on the website of Jan Karel: http://www.jkp-ads.com/.

For each slicer that you add to your workbook, Excel adds an object SlicerCache too, which controls which pivot table slicer controls.

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

Hierarchy of cutting and his family

The following code enumerates all slicer cache in the workbook:

Sub MultiplePivotSlicerCaches ()
Dim oSlicer as Slicer
Dim oSlicercache as SlicerCache
Dim opt as a pivot table
Dim SSL as worksheet
For each oSlicercache In ThisWorkbook. SlicerCaches
For each opt-in PivotTables oSlicercache.
oPT.Parent.Activate
MsgBox oSlicercache .name & "" & oPT.Parent.Name
Next
Next
End Sub

As soon as it occurs more than one PivotTable PivotTable dialog box of a cutting connections, slicing machines concerned will share a single object of SlicerCache. The SlicerCache object will be removed from the collection. This explains why can go back by taking away all but a pivot table in the dialog box: all together now slicers will be changed by changing PivotTables checked on them. In turn, each selected PivotTable becomes part of the collection of the SlicerCache rest PivotTables.

If you decide to select Slicer1 and change the pin connections by checking whether Pivottable1, Pivottable2, and a slicercache is deleted (the one that belongs to the PivotTable is controlled to add the current slicer). So the hierarchy changes to:

Hierarchy changed slicers

So that it is Slicer1 that Slicer2 check PivotTables, 1 and 2. Slicing machines 1 and 2 shall be synchronized too, because in reality it is the SlicerCache that has changed with the slicing machine. So the hierarchy in the picture above is not completely true.

It's pretty easy to change the appearance of the button using some VBA:

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

Note that the numbers do not coincide with what is shown in the Ribbon. Apparently, the unit of measure distinguishes between VBA and the Ribbon.

Change certain aspects of your slicer using VBA is not hard to do. In fact, the macro recorder is relatively easy to find out how it works. After changing some settings and doing a bit of restructuring I:

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

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

--Jan Karel Pieterse

View the original article here