Select multiple items in a slicer

Question

I have working code which selects single items from a slicer, however it doesn't work for multiple items.

The selection is set up by reading which cells in a range are in bold, and populating an array of strings, STP(46), populating up until STP(k). This works fine.

Then the code is supposed to deselect all items in the slicer which aren't in STP, and select those which are. This works for one selection but not for multiple selections - it erroneously selects all items up until the last item to be selected.

With ActiveWorkbook.SlicerCaches("Slicer_STP_Name")
    For i = 1 To .SlicerItems.Count
        For j = 1 To k
            If .SlicerItems(i).Selected And .SlicerItems(i).Caption <> STP(j) Then .SlicerItems(i).Selected = False
        Next j
    Next i

    For i = 1 To .SlicerItems.Count
        For j = 1 To k
            If .SlicerItems(i).Caption = STP(j) Then .SlicerItems(i).Selected = True: Exit For
        Next j
    Next i
End with

So instead of selecting, say, the 2nd and 4th item in the slicer, it selects the 1st, 2nd, 3rd, 4th, and deselects the rest.

I need to use a looped technique like this because I need to be able to use this code with multiple slicers with different cache names but the same list of items.

I've looked everywhere, and the code above is even from a solution from another question on here. Any help greatly appreciated!

Brad Brad · Accepted Answer · 2016-06-02T16:30:39

You can use a dictionary to make the process a little more smooth

With ActiveWorkbook.SlicerCaches("Slicer_test_id")
    Dim i
    For i = 1 To .SlicerItems.Count

        If .SlicerItems(i).Selected And Not stp.Exists(.SlicerItems(i).Caption) Then
            .SlicerItems(i).Selected = False
        End If

    Next i

    For i = 1 To .SlicerItems.Count
        If stp.Exists(.SlicerItems(i).Caption) Then
            .SlicerItems(i).Selected = True
        End If

    Next i
End With

I'm not totally clear on why you need the first loop. I'm reading "Then the code is supposed to deselect all items in the slicer which aren't in STP, and select those which are." as "select only those otems in STP and deselect all others" which this reduced code will do:

With ActiveWorkbook.SlicerCaches("Slicer_test_id")
    Dim i
    For i = 1 To .SlicerItems.Count
        .SlicerItems(i).Selected = stp.Exists(.SlicerItems(i).Caption)
    Next i
End With

populating the Dictionary is super easy

Dim stp As New Dictionary
stp.Add "73148", "73148"
stp.Add "73150", "73150"
stp.Add "73159", "73159"

You need to reference the Microsoft Scripting Runtime

$C:\Windows\SysWOW64\scrrun.dll$

note that if you don't see Microsoft Scripting Runtime in the list you can Browse to C:\Windows\SysWOW64\scrrun.dll

Select multiple items in a slicer

1 Answers