Skip to content
January 16, 2015 / Erik Svensen

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.”

http://msdn.microsoft.com/en-us/library/office/ff822747(v=office.15).aspx

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

Sub SetSelection()

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].&[2005]” ‘Name of item you want selected

End Sub

 

Run the code (or modify to your need) to select the item.

You can download an example file here.

Advertisements

13 Comments

Leave a Comment
  1. SutoCom / Jan 20 2015 3:25 pm

    Reblogged this on SutoCom Solutions.

  2. Mark / Jul 4 2015 2:27 am

    hummm, I’m trying to figure out how to pass back MULTIPLE selections (ala, 2005 & 2006, but not [ALL]) on an PowerPivot(OLAP)-Slicer to a non-OLAP Slicer with identical SlicerItems…. any Ideas?

    • Erik Svensen / Jul 4 2015 10:04 pm

      Hi Mark,

      I think this should do the trick

      Sub SetMultipleSlicerItems()

      Dim scOlAP As SlicerCache

      Dim scList As SlicerCache

      Dim sO As Slicer
      Dim sL As Slicer

      Dim si As SlicerItem

      Set scOlAP = ActiveWorkbook.SlicerCaches(“Slicer_Product1”)
      Set scList = ActiveWorkbook.SlicerCaches(“Slicer_Product”)

      Set sO = scOlAP.Slicers(1)

      Set sL = scList.Slicers(1)

      Dim i As Integer
      Dim svalue As String

      For i = 1 To UBound(scOlAP.VisibleSlicerItemsList)

      svalue = Replace(Replace(scOlAP.VisibleSlicerItemsList(i), “[Table1].[Product].&[“, “”), “]”, “”)

      For Each si In scList.SlicerItems
      If svalue = si.SourceName Then
      si.Selected = True
      Else
      si.Selected = False
      End If

      Next

      Next

      End Sub

      The Slicer_Product1 is the OLAP/PowerPivot slicer and the Slicer_Product is linked to a table in the workbook.

      Hope this solves your problem

      Erik

  3. Mark / Jul 6 2015 6:13 am

    Erik, very awesome of you to reply…. I’m almost there, but the behavior isn’t quite right, ClearManualFilters on the non-OLAP slicer work to improve but there are still some odd glitches… sample workbook is here:
    https://www.dropbox.com/s/pobonolmxf555yl/SyncSlicerProblem_07.05_2015.xlsm?dl=0

    When any single selection is made from the OLAP slicer, the Regular Slicer follows it correctly. When you hold CTRL when all Slicer Items are selected, only deselecting D & E will Follow correctly; whereas, deselecting A,B,or C will not Follow into the Regular Slicer. Selecting A and then holding CTRL and Selecting B will Follow correctly; however, selecting B and then holding CTRL and Selecting C will not Follow correctly. There are many other instances that do not Follow correctly. Thoughts?

    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim scOLAP As SlicerCache
    Dim scList As SlicerCache
    Dim sO As Slicer
    Dim sL As Slicer
    Dim si As SlicerItem
    Dim i As Integer
    Dim svalue As String

    Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
    Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

    scList.ClearManualFilter

    Set sO = scOLAP.Slicers(1)
    Set sL = scList.Slicers(1)

    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)

    svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), “[CleanedData].[RegionCode].&[“, “”), “]”, “”)

    For Each si In scList.SlicerItems
    If svalue = si.SourceName Then
    si.Selected = True
    Else
    si.Selected = False
    End If

    Next

    Next

    End Sub

    • Erik Svensen / Jul 6 2015 10:18 am

      Hi Mark,

      It was due to the order in which the code deselected and selected the items – when all items gets deselected the slicer will return to all.

      I modified the code to do it in a slightly different way – using a function to check whether the item in the synched slicer should be selected or not

      Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
      Dim scOLAP As SlicerCache
      Dim scList As SlicerCache
      Dim sO As Slicer
      Dim sL As Slicer
      Dim si As SlicerItem
      Dim i As Integer
      Dim j As Integer
      Dim svalue As String

      Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
      Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

      scList.ClearManualFilter

      Set sO = scOLAP.Slicers(1)
      Set sL = scList.Slicers(1)

      For j = 1 To scList.SlicerItems.Count

      scList.SlicerItems(j).Selected = CheckSelected(scList.SlicerItems(j).SourceName, scOLAP)

      Next

      End Sub

      Function CheckSelected(testFor As String, sc As SlicerCache)

      For k = 1 To UBound(sc.VisibleSlicerItemsList)
      If testFor = Replace(Replace(sc.VisibleSlicerItemsList(k), “[CleanedData].[RegionCode].&[“, “”), “]”, “”) Then
      CheckSelected = True
      Exit For
      End If
      Next

      End Function

      I have tested and it seems to work in the scenarios you mention.

      Hope this solves your problem

      BR
      Erik

  4. Mark / Jul 6 2015 4:23 pm

    Hi Erik, yes that did work with a little help from the Microsoft Developers forum I simplified the code a bit to omit the function. For all other reading these comments the code below will help you Sync an OLAP slicer to a non-OLAP slicer and is an excellent workaround for the inherent problem with MDX and not being able to “ShowDetails” on data that has been filtered by multiple critrea (ala, two selections from a single slicer).

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim scOLAP As SlicerCache
    Dim scList As SlicerCache
    Dim sO As Slicer
    Dim sL As Slicer
    Dim si As SlicerItem
    Dim i As Integer
    Dim svalue As String
    Dim ar() As String

    Set scOLAP = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode”)
    Set scList = ActiveWorkbook.SlicerCaches(“Slicer_RegionCode1”)

    scList.ClearManualFilter

    Set sO = scOLAP.Slicers(1)
    Set sL = scList.Slicers(1)
    ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
    For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
    svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), “[CleanedData].[RegionCode].&[“, “”), “]”, “”)
    ar(i) = svalue
    Next
    For Each si In scList.SlicerItems
    If UBound(Filter(ar, si.SourceName)) < 0 Then
    si.Selected = False
    End If
    Next
    End Sub

    • Kyle / Apr 15 2016 7:59 pm

      This is great Mark – only caution is that the quotes do not copy and paste correctly into VBA. I’m using this as a base for passing multiple slicer selections between OLAP slicers and had previously found it challenging to store an array of OLAP slicer selections.

      • Mark David Gaal / Apr 17 2016 4:34 pm

        You’re very welcome Kyle, ditto on the quotation problem.

  5. Mark / Jul 6 2015 4:24 pm

    Erik thank you for this, I was stuck for about 4 days because of this, was about to rebuild my entire workbook to have it based directly off the the data in the regular excel table, lose my calculated fields, and KPIs. I appreciate the help immensely.

  6. Rafał / Mar 1 2016 2:29 pm

    Nice article. It was helpful… Thanks!!!

  7. detaildevil / Aug 13 2016 1:17 am

    Spent all afternoon trying to figure out vba for pivot table slicers with external ranges. Not much out there, so I was glad to find your info. It solved my problem – thanks so much for taking the time to post your experience.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: