CODEWITHSUNDEEP
excelvba
TobiasKnudsen
TobiasKnudsen

Reputation: 579

Excel VBA - Return selected element from slicer

I have a slicer with a single selected element, and i just need to dim a variable with the corresponding text of the selected item from my pivot slicer. I've tried this with no luck:

Dim i As Integer

For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_HeaderTitle").Slicers.Count
MsgBox ("Level" & CStr(i) & "; – > " & ActiveWorkbook.SlicerCaches("Slicer_HeaderTitle").Slicers(i).Name)
Next i

Upvotes: 0

Views: 3089

Answers (1)

zaptask
zaptask

Reputation: 707

The problem here is you are trying to iterate over slicers rather than slicer items.

You can try something like this

Dim o_slicer_item As Object

For Each o_slicer_item In ActiveWorkbook.SlicerCaches("Slicer_HeaderTitle").SlicerItems
    If o_slicer_item.Selected = True Then MsgBox o_slicer_item.Name
Next

This will simply display an item name if it's selected (so it will show a msgbox for each selected item, regardless of the count). If you want to show just one (first, last or n-th) you'll need to modify it to break the loop in a correct point.

Upvotes: 1

Related Questions