CODEWITHSUNDEEP
vbaexcelexcel-2010
whytheq
whytheq

Reputation: 35557

VBA to change slicer selection current selected item

The below behaves quite strangely.
It's aim is to leave the slicer with only the item specified (in this case "Smith") with all other names not selected.
Most of the time it works but sometimes more than one item will be left selected.

What is wrong with the below and how do I achieve the required behaviour?

Sub myRoutine()
    unselectAllBut "Slicer_InitialAcc_Surname", "me"  
End Sub

Public Sub unselectAllBut(slicerName As String, newSelection As String)

Dim si As Object
For Each si In ActiveWorkbook.SlicerCaches(slicerName).SlicerItems
    si.Selected = (si.Caption = newSelection)
Next si

End Sub

Second attempt which doesn't work either:

Public Sub unselectAllBut(slicerName As String, newSelection As String)

Dim i As Integer
With ActiveWorkbook.SlicerCaches(slicerName)
    For i = 1 To .SlicerItems.Count
        .SlicerItems(i).Selected = (.SlicerItems(i).Caption = newSelection)
    Next i
End With

End Sub

Maybe the data is causing the problem. It looks like the following:

enter image description here

EDIT

The following seems to work. I select all items first which seems like over-kill:

Public Sub unselectAllBut(slicerName As String, newSelection As String)

Dim i As Integer

With ActiveWorkbook.SlicerCaches(slicerName)
    For i = 1 To .SlicerItems.Count
        .SlicerItems(i).Selected = True
    Next i
    For i = 1 To .SlicerItems.Count
        .SlicerItems(i).Selected = (.SlicerItems(i).Caption = newSelection)
    Next i
End With

End Sub

Upvotes: 2

Views: 28563

Answers (1)

Tamas Karacsony
Tamas Karacsony

Reputation: 96

A bit faster way:

  • first set the new selection
  • second clear all others

Public Sub unselectAllBut(slicerName As String, newSelection As String)


  Dim i As Integer

  With ActiveWorkbook.SlicerCaches(slicerName)

    For i = 1 To .SlicerItems.Count
      If .SlicerItems(i).Caption = newSelection Then .SlicerItems(i).Selected = True: Exit For
    Next i
    For i = 1 To .SlicerItems.Count
      If .SlicerItems(i).Selected And .SlicerItems(i).Caption <> newSelection Then .SlicerItems(i).Selected = False
    Next i
  End With

End Sub

Upvotes: 8

Related Questions