CODEWITHSUNDEEP
excelvbamacos
Eric Shreve
Eric Shreve

Reputation: 139

Checking to see if any values selected in Slicer

I have some slicers on a workbook. For some of the slicers I would like to check to see if anything in that slicer is selected, and, if so, go do some stuff elsewhere in the workbook. I'm using Excel on Mac. Below is what I've tried. It give me an error of "Invalid procedure call or argument" on the "For Each" line. Any ideas on how to do this? Where I have MsgBox is where I will be putting my code for doing stuff elsewhere in the workbook.

Sub Check_Other_Slicers()
Dim si As SlicerItem

For Each si In ActiveWorkbook.SlicerCaches("District").SlicerItems

    If si.Selected = True Then
        MsgBox "x"
    End If
Next si
End Sub••••ˇˇˇˇ

Upvotes: 0

Views: 922

Answers (1)

IndieMamba
IndieMamba

Reputation: 73

Use the following:

Sub subCheckSlicerSelection()

    Dim intCnt As Integer
    
    With ThisWorkbook.SlicerCaches("Slicer_Test")
        
        'Looping through the slicer items
        For intCnt = 1 To .SlicerItems.Count
            
            'Check if the slicer item is selected
            If .SlicerItems(intCnt).Selected Then
                
                'Show the selected value
                MsgBox .SlicerItems(intCnt).Caption
                
            End If
        
        'Next slicer item
        Next intCnt
    
    End With

End Sub

Upvotes: 2

Related Questions