K.SlicerCache.SortUsingCustomLists = True K.SlicerCache.SortItems = xlSlicerSortDescending 'OR xlSlicerSortAscending K.SlicerCache.CrossFilterType = xlSlicerNoCrossFilter ' OR xlSlicerCrossFilterShowItemsWithNoData / xlSlicerCrossFilterShowItemsWithDataAtTop You can sort, toogle header display, filter setting and change other settings of the slicer using VBA. MsgBox "Changed Slicer Field Row Height and Width"Ī few more important things. 'OR use the 'j("My_Region") syntax as shown above MsgBox "Changed Slicers Row Height and Width" K.Shape.ScaleHeight 0.6, msoFalse, msoScaleFromTopLeft K.Shape.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft Set k = j.Add(ActiveSheet,, "My_Region", "Region", 0, 0, 200, 200) ' Specify the dimensions here In the example below, we first create a slicer, then move the slicer around by changing its top and left coordinates and then finally adjust the slicer row height and the column width. It is also possible to adjust the height and width of the slicer items. Other things like the height, width, top and left coordinates can be changed – either at the time of creation of the slicer or at runtime. Set k = j.Add(ActiveSheet,, "My_Region", "Region", 0, 0, 200, 200)Ĭhange Slicer Position, Height, Width and Style Using VBA Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers So what to do when the slicer has done its job? Dispose it. K.Caption = "My Caption" 'Or use j(1).Caption = "My Caption" K.Name = "Slicer_Name" 'Or use j(1).Name = "Slicer_Name"
It makes great sense to provide descriptive and unique captions to slicers when you have more than one slicer based on the same field for multiple pivot tables. You can also modify the slicer name and caption. K.SlicerCache.SlicerItems("West").Selected = True I("Region").SlicerItems("West").Selected = True K.SlicerCache.SlicerItems(1).Selected = False K.SlicerCache.SlicerItems("West").Selected = False I("Region").SlicerItems("West").Selected = False Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers Let’s see how we can turn on (and off) the item “West” within that pivot field using VBA.
Create slicer in excel code#
You can extend the same code to any other field of the pivot table – visible or otherwise. We will first construct a slicer based on the “Region” field and then gradually move into carrying out other operations in it using VBA. Imagine a pivot table that shows the sales of a number of product categories in various regions. Name, Caption, Top, Left, Width and Height are used to specify the other parts of the hierarchy. SourceField is the name of the column header of the field for which the slicer is being created. Source is usually the name of the pivot table from which the slicer is being created. My_Slicer_Caches is an object of type SlicerCaches
Set My_Slicer = Add(SlicerDestination, ,) Set My_Slicers = Add(Source, SourceField, ) Set My_Slicer_Caches = ActiveWorkbook.SlicerCaches
Let’s look at the slicer hierarchy – a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each pivot table field) and finally each slicer having one slicer object. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Slicers, as you would know, are the latest introduction to Excel. Here is some VBA code that can help you automate working with a slicer in Excel.
0 kommentar(er)
