Excel VBA multiple selection ListBox check if nothing is selected

Question

I am trying to figure out the best way, on how to use Excel ListBox with multiple selections and have a simple VBA code for it to filter multiple sheets based on what is selected in the ListBox.

The code that I have right now is below. At the moment it does pretty much exactly what I need - checks if there is any filter in the sheets, cleans it if so, and then filters out the selected values. But what I need it to do as well, is that there is no value selected at all, it should clean the filters in 4 sheets and exit sub.

The thing is, that I get an "invalid procedure" error if I try to run it when nothing is selected. I did try to add an Else statement and another If to check If .Listindex = -1, but both of the options gave the exact same error. As this needs to be a multiple selection list, I found that it also needs to loop while checking if nothing is selected, but yet again, had the same error.

How can I improve this code and add the required functionality?

Sub filter1()

Dim MyArray() As String
Dim Cnt As Long
Dim r As Long

Cnt = 0
With Me.ListBox1
    If .ListIndex <> -1 Then
        For r = 0 To .ListCount - 1
            If .Selected(r) Then
                Cnt = Cnt + 1
                ReDim Preserve MyArray(1 To Cnt)
                MyArray(Cnt) = .List(r)
            End If
        Next r
    End If
End With

With Sheet1
    If .FilterMode Then .ShowAllData
    .Range("A2:Y1037").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End With

With Sheet3
    If .FilterMode Then .ShowAllData
    .Range("A2:AB1037").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End With

With Sheet4
    If .FilterMode Then .ShowAllData
    .Range("A2:Z1037").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End With

With Sheet5
    If .FilterMode Then .ShowAllData
    .Range("A2:Z1037").AutoFilter field:=2, Criteria1:=MyArray, Operator:=xlFilterValues
End With

End Sub

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-04-13T08:30:47

Check if cnt is 0 before the line which says With Sheet1 and if cnt is 0 that means nothing was selected in the ListBox, prompt the user and use exit sub like below...

If cnt = 0 Then
    MsgBox "No item was selected in the ListBox." & _
            "Please select an item and then try again...", vbCritical, "No Item Selected"
    Exit Sub
End If

With Sheet1

End With

Excel VBA multiple selection ListBox check if nothing is selected

2 Answers