Excel VBA - return selected item to slicer

Question

I have a slicer called "Slicer_HeaderTitle". I just need to be able to darken a variable in VBA with the value of the selected item. I will select only one item at a time.

I had a lot of problems with dynamically selecting and deselecting items from my slicer through VBA, since my pivot table is connected to an external data source. I do not know if this applies to this exact example, but this table is linked to the same external data source.

I used to have one line of code that could return this value, but now all I could find was to iterate over each item in the slicer and check if it was selected or not. I hope to avoid this, as I only have 1 selected item 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 state:

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 Dr. OSwaldo for helping me a lot!

Answer 2

Well, to find the error, we will take a step back, delete my function and try to scroll through the elements:

 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 my two cents. The set of visible slicer elements can be reduced by both independent actions:

1. User Selecting Elements in Slicer A. Use the .Selected method to capture these elements.
2. Selecting elements in slicer B, which as a result shortens the list of slicer A. To capture these elements, use the .HasData method.

Please note that you can only see two Slicer_Products elements (apples, bananas), because some other Slicer_Product_Type have an active filter for fruits . The sI.Selected method would still return the entire list of products apples, bananas, carrots ...

If you want both restrictions to be set, intersect both sets. I changed the code of TobiasKnudsen (great answer!) So that it returns a list of elements compressed by both of the above limitations. If sI.Selected = True And sI.HasData = True Then this 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