kanmabeibi70
04-06-2011, 01:08 AM
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,Office Professional Plus 2007 (http://www.office2007key.eu), 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,Office 2010 Pro Key (http://www.office2010key.in/), 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,Office Professional Plus (http://www.office2010key.in/), 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
'xlSlicerCrossFilterShowItemsWithDataAtTop:
' Visually indicate items with no data,Windows 7 X86 (http://www.windows7-key.us/), 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 Conclusion
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,Office 2007 Professional (http://www.office2007key.ca)!
-- Jan Karel Pieterse <div
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,Office Professional Plus 2007 (http://www.office2007key.eu), 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,Office 2010 Pro Key (http://www.office2010key.in/), 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,Office Professional Plus (http://www.office2010key.in/), 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
'xlSlicerCrossFilterShowItemsWithDataAtTop:
' Visually indicate items with no data,Windows 7 X86 (http://www.windows7-key.us/), 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 Conclusion
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,Office 2007 Professional (http://www.office2007key.ca)!
-- Jan Karel Pieterse <div