listindex=0 when first item selected in a multiselect listbox on a userform?

Hello,

I have a multiselect list box that I only want code to run on if at least one option is highlighted. The below works for every scenario except if only the very top option listed in the listbox is selected. Is there another way I can pass over my code if nothing is selected? Is it strange that selecting the top option only returns a listindex = 0 ? It seems like it should be 1.

Private Sub CommandButton13_Click()


Dim l As Long
Dim m As Long
Dim countrylist As String


If ListBox3.ListIndex <> 0 Then
    Dim j As Long
    Dim MyArr As Variant
    For j = 0 To ListBox3.ListCount - 1
        If ListBox3.Selected(j) Then
            MyArr = MyArr & Me.ListBox3.List(j, 0) & ","
        End If
    Next j
    MyArr = Left(MyArr, Len(MyArr) - 1)
    countrylist = MyArr
    MyArr = Split(MyArr, ",")
    Rows("1:1").AutoFilter _
        Field:=1, Criteria1:=MyArr, Operator:=xlFilterValues
End If

Thanks!

Does

If Listbox3.Value = "" Then

do the trick ?

Nope. ListBox3.Value= Null for anything (or nothing) selected. Thanks for the idea though!

Originally Posted by JosephP

the listindex starts at 0

Right, but doesn't the listindex=0 also if nothing is selected? How can I indicate in my code that I only want it run if something is selected

Originally Posted by Norie

How does the code not work if the top item is selected?

Does it only not work when that's the only item selected?

Yes, it only does not work if that is the only item selected. If I select the 2nd item in the list, or if I select the 1st item and other items the code runs. It seems the problem is that the listindex = 0 both when nothing is selected, and when just the first option is selected

Originally Posted by JosephP

also if the listbox is multiselect the listindex can have a value >=0 even if no item is actually selected

Interesting. Do you have any ideas how I can differentiate between nothing selected, and anything selected?

Originally Posted by Norie

The index of a listbox is 0-based, ie it starts at 0.

Usually when nothing is selected the ListIndex is -1 but you can't really use ListIndex reliably with a multiselect listbox, which is what I think you have.

What exactly is happening when you only select the first item?

Does the code error?

Does the filter not filter correctly?

Also, is it only the first item that has the problem? It doesn't happen when another item is the only one selected?

When the first item is selected the code does not error. I've walked through it step by step, and when just the first item is selected the listindex for listbox3 = 0. As such, the code works just as if nothing were selected, where the code skips everything within the parent "IF" statement.

Also, yes, this is the only scenario where I have a problem. If I select the 2nd item, or any other item individually, or any combination of options the code runs.

In case it is helpful, here is the code where I populate the listbox:

  
    Set country = CreateObject("Scripting.Dictionary")
    With country
        .CompareMode = 1
        For Each e In Sheets("Sheet1").Range("A2:A20000").Value
            If e <> "" Then .Item(e) = Empty
        Next
        Me.ListBox3.List = .keys
    End With

Originally Posted by Norie

So, what should happen when only the first item is selected?

Perhaps you could upload a sample workbook so we can see what's going on?

PS I think it might be something to do with Operator:=xlFilterValues which doesn't appear in the recorded code when you filter on one item.

If just the first item is selected, I would like the code to run. The "If ListBox3.ListIndex <> 0 Then" statement is my attempt to skip over the listbox if nothing is selected, and run the code if anything is selected. I will try to upload a workbook, but I have had trouble doing this in the past.

Originally Posted by Norie

Don't check the ListIndex, as I said it's not reliable with a multiselect listbox.

To see if nothing has been selected then check the length of the string MyArr after the loop.

If it Len(MyArr) = 0 then no values have been selected, so you can exit the sub.

Thanks! Let me see if I can get this to work -- sounds like it should work perfectly. I'll post my results shortly

I don't think I will be able to upload a sample. I get the following error: 403 [IOErrorEvent type="ioerror" bubbles=false cancelable=false eventPhase=2 text="Error2038"]

sorry! I can try to explain my spreadsheet and userform if that might help

Thanks, Norie! With your advice, I changed my code to the following:

    Dim j As Long
    Dim MyArr As Variant
    For j = 0 To ListBox3.ListCount - 1
        If ListBox3.Selected(j) Then
            MyArr = MyArr & Me.ListBox3.List(j, 0) & ","
        End If
    Next j
If Len(MyArr) <> 0 Then
    MyArr = Left(MyArr, Len(MyArr) - 1)
    countrylist = MyArr
    MyArr = Split(MyArr, ",")
    Rows("1:1").AutoFilter _
        Field:=1, Criteria1:=MyArr, Operator:=xlFilterValues
End If

According to my testing, this works perfectly.