Excel VBA - Return selected element in slicer

Question

I have a slicer called 'Slicer_HeaderTitle'. I simply need to be able to dim a variable in VBA with the value of the selected element. I'll only have one element selected at a time.

I've had a lot of problems with selecting and de-selecting elements from my slicer dynamically via VBA, since my pivot table is connected to an external data-source. I don't know if this is relevant for this exact example, but this table is connected to the same external data-source.

I used to have a single line of code, which could return this value, but all i could find now requires you loop through each element in the slicer and check if it's selected or not. I hope to avoid this, since I only have 1 selected element at a time.

' This is what I'm trying to achieve.
Dim sValue as String
sValue = ActiveWorkbook.SlicerCaches("Slicer_HeaderTitle").VisibleSlicerItems.Value

msgbox(sValue)
'Returns: "Uge 14 - 2016 (3. Apr - 9. Apr)"

---

Current Status:

Answer 1

This is what i did:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

Set SL = ActiveWorkbook.SlicerCaches(SlicerName).SlicerCacheLevels(1)
For Each sI In SL.SlicerItems
    If sI.Selected = True Then
         GetSelectedSlicerItems = (sI.Value)
    End If
Next
End Function


Dim sValue As String
sValue = GetSelectedSlicerItems("Slicer_HeaderTitle")

Thanks to Doktor OSwaldo for helping me a lot!

Answer 2

Ok to find the error, we will take a step back, delete my function and try Looping through the items:

Dim sC As SlicerCache 
Dim SL As SlicerCacheLevel 
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Dates_Hie") 
Set SL = sC.SlicerCacheLevels(1)
For Each sI In SL.SlicerItems    
  sC.VisibleSlicerItemsList = Array(sI.Name) 
Next

Answer 3

I would like to put in my two cents. The set of visible slicer items may be shrunk by both independent actions:

1. User selection of items in slicer A. To capture those items, use .Selected method.
2. Selection of items in slicer B which in consequence shrinks the list of slicer A items. To capture those items, use .HasData method.

Note that you may see only say two items of Slicer_Products (apples, bananas) because some other slicer Slicer_Product_Type has active filter on fruits. The method sI.Selected would still return the whole list of products apples, bananas, carrots...

If you want both limitations to be in place then make intersection of both sets. I have modified TobiasKnudsen code (excellent answer!) to return the list of items shrunk by both above limitations. If sI.Selected = True And sI.HasData = True Then is the key line in this code.

Option Explicit

Sub TestExample()
    Dim MyArr() As Variant
    MyArr = ArrayListOfSelectedAndVisibleSlicerItems("Slicer_A")
    'now variable MyArr keeps all items in an array
End Sub

Public Function ArrayListOfSelectedAndVisibleSlicerItems(MySlicerName As String) As Variant
    'This function returns an array of the limited set of items in Slicer A
    'Limitation is due to both:
    '(1) direct selection of items by user in slicer A
    '(2) selection of items in slicer B which in consequence limits the number of items in slicer A

    Dim ShortList() As Variant
    Dim i As Integer: i = 0 'for iterate

    Dim sC As SlicerCache
    Dim sI As SlicerItem 'for iterate

    Set sC = ThisWorkbook.Application.ActiveWorkbook.SlicerCaches(MySlicerName)
    For Each sI In sC.SlicerItems
        If sI.Selected = True And sI.HasData = True Then 'Here is the condition!!!
                'Debug.Print sI.Name
            ReDim Preserve ShortList(i)
            ShortList(i) = sI.Value
            i = i + 1
        End If
    Next sI
    ArrayListOfSelectedAndVisibleSlicerItems = ShortList
End Function

Answer 4

Sub Demo()
Dim i As Integer
With ActiveWorkbook.SlicerCaches("Slicer_Country")
    For i = 1 To .SlicerItems.Count
        If .SlicerItems(i).Selected Then
            Sheets("Pivot Sheet").Range("I" & i) = SlicerSelections & " " & .SlicerItems(i).Value
        End If
    Next i
End With
End sub

Answer 5

This is how I managed to identify the selected element on a slicer.

The answer by TobiasKnudsen above did not work for me as I got an error stating the data source needed to be an OLAP source.

My data is an excel table and this is the code that worked:

Dim val as Boolean
val = ThisWorkbook.SlicerCaches("Slicer_MYSLICER").VisibleSlicerItems.Item("CS").HasData

In my case, the slicer had only 3 items so I repeated the line above with a different string in item()

So, where val was true, that was the item that was currently selected.