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