Anyone know how to write just the clicked on items in a slicer to an cell (or an array)?
Writing slicer selections
- ryangus
- Thread is marked as Resolved.
-
-
-
Try this UDF
Code
Display MorePublic Function GetSelectedSlicerItems(SlicerName As String) As String Dim oSc As SlicerCache, oSi As SlicerItem, lCt As Long On Error Resume Next Application.Volatile Set oSc = ThisWorkbook.SlicerCaches(SlicerName) If Not oSc Is Nothing Then For Each oSi In oSc.SlicerItems If oSi.Selected Then GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", " lCt = lCt + 1 End If Next If Len(GetSelectedSlicerItems) > 0 Then If lCt = oSc.SlicerItems.Count Then GetSelectedSlicerItems = "All Items" Else GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2) End If Else GetSelectedSlicerItems = "No items selected" End If Else GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found" End If End Function
Then in the cell where you want to show the selected slicer item(s), if slicer allows multiselect then each selected slicer item will show separated by a comma.=GetSelectedSlicerItems("Name of Your Slicer")
The slicer Name must be enclosed in the double quotes.
-
Thanks KjBox - I just came in here to post the same solution I find through a google search! It works pretty good, but a bit resource hungry perhaps.
However, one thing it is missing is capturing deselected items (using the CTRL button). I'm wondering if/how this would be possible to capture this?
-
I've noticed that using the GetSelectedSlicerItems macro is very resource hungry. Slicers that were taking a few seconds to refresh the data are now taking tens of seconds. I'm wondering if it's because I've set it up on too many slicers (12 all up).
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!