Setting selected item in a slicer connected to a Power Pivot table via VBA
Yesterday a colleague asked me whether it was possible to select an item in a slicer via VBA and I replied “Of course” – but that was before I studied the documentation about the “Selected” property of the SlicerItem object –
“Returns or sets whether the slicer item is selected. Read/write for slicers connected to non-OLAP data sources. Read-only for slicers connected to OLAP data sources.”
Now that was a big surprise – and I thought why not try to work around this.
First idea was to add a page field (Filter) because changing a page field will set the selected slicer items to the items selected in the page field (Filters)
However, writing a macro to do this seemed a bit over kill – so I continued reading about slicers and their properties
Moreover, it turned out that if we look at the SlicerCache it has a property called “VisibleSlicerItemsList”
That is not read/only – but Read/Write
Therefore, we can use this property to set the selected item in a slicer connected to an OLAP source – and here is the code to set a selected item
Dim sc As SlicerCache
Dim s As Slicer
Set sc = ActiveWorkbook.SlicerCaches(“Slicer_DimDate.CalendarYear”) ‘ Name of slicer
Set s = sc.Slicers(1)
sc.VisibleSlicerItemsList = “[Query1].[DimDate.CalendarYear].&” ‘Name of item you want selected
Run the code (or modify to your need) to select the item.
You can download an example file here.