Writing Slicer Items to Sheet- Modifying Existing Code

[liquidmettle]

Hey All!

Since Bakerman2 was kind enough to solve one of my long-running problems with the dashboard I'm working on, I have another to ask the community.

With the following code, I am able to capture the selected slicer filters and have them update in a cell on my sheet in real time.

My problem is that I have four slicers I want to do this with (each slicer's filters writing to a different cell), and I have not determined the way to do so yet.

My slicer names are: Slicer_Month, Slicer_TWP_Code, Slicer_Activity_Code, Slicer_Program_Code.

The cell formula for the first slicer would be:

=GetSelectedSlicerItems("Slicer_Month")

The Public function that makes it possible is:

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches("Slicer_Month")
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            ElseIf oSi.HasData = False Then
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All Available Data Selected"
            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

I tried repeating this function four times with the slicer names but that resulted in Ambiguous name errors. I also tried inputting multiple slicer names into the slicercaches area of the sheet but that also did not work.

Any thoughts from my Excel Forum Heroes?

[dflak]

This line:
Set oSc = ThisWorkbook.SlicerCaches("Slicer_Month")
is hard coded. Since you pass the slicer name to the function change it to read
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)

[liquidmettle]

Hi dflak!

I ended up contacting the original code writer and he returned my question with an answer.

You are absolutely correct, however, (and I didn't realize it till his second solution) that "SlicerName" didn't mean "input your slicer name" ha.

I've been meaning to get back here to post the solution so I'm glad your reply sent me a reminder.

Here was his response:

Here is an alternative version which has an extra argument with which you can choose to either get all items in a single cell, or return a list of them. For the latter to work you must array-enter the formula into as many cells as you expect to have sliceritems.

Public Function GetSelectedSlicerItems(SlicerName As String, Optional ReturnArray As Boolean = False) As Variant 
    Dim oSc As SlicerCache 
    Dim oSi As SlicerItem 
    Dim lCt As Long 
    On Error Resume Next 
    Application.Volatile 
    Set oSc = ActiveWorkbook.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 
            ElseIf oSi.HasData = False Then 
                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) 
                If ReturnArray Then 
                    GetSelectedSlicerItems = Application.Transpose(Split(GetSelectedSlicerItems, ",")) 
                End If 
            End If 
        Else 
            GetSelectedSlicerItems = "No items selected" 
        End If 
    Else 
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found" 
    End If 
End Function

Since I have four slicers, I entered this formula (below) into four cells (each with the correct slicer name) and then Array-Entered each one separately.

And Voila.

=GetSelectedSlicerItems("Slicer_Month")