VBA (Excel) ActiveX ListBox Change Event recursive behaviour

Question

I'm not a VBA programmer so I apologize in advance if some of my terminology in this question is incorrect. A colleague of mine wanted to clear the selection from a list box as soon as it was selected. After some googling we found one way to do it was via the Change event. Initially we tried:

Private Sub ListBox1_Change()
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = False
    Next i
End Sub

However, it seemed that setting the Selected property to False triggers a Change event on the list box and this effectively becomes an infinite loop and causes Excel (2007) to crash. Given we knew there were two entries we also tried:

Private Sub ListBox1_Change()
    ListBox1.Selected(0) = False
    ListBox1.Selected(1) = False
End Sub

And that works! Though we'd expect the same behaviour - for the setting of the Selected property to cause the Change event to trigger again and to get an infinite loop.

However it seems that once e.g. ListBox1.Selected(0) = False the Change event is re-triggered but in that iteration it doesn't retrigger on this line - i guess because it knows that this Selected property has already been set to to False for this item, so nothing is changing.

But if that is the case then we'd also expect that behaviour in the first solution .. so it seems there is some difference in saying ListBox1.Selected(i) = False versus specifying the actual item index directly (rather than via the variable i).

Does anyone know the reason for this behaviour? Hope the question makes sense i've tried to explain it as best I can.

Thanks Amit

Solution

I'm a year late to the party but I hope this will help others. I was having problem with Listbox1_Click() infinite loop rather than change(). However, I think this can be a viable solution to both.

Whenever I called Listbox1.Selected(i) = True, it would trigger it as a Click() or a Change(). In my click() routine, there are certain index that will cause the entire list to repopulate itself with a new list and reselect itself. This causes the infinite loop when it reselected itself. It took me a day to troubleshoot, but in the end the solution was not to use click() event; instead, I used MouseDown() event with a little calculation. This eliminate the use of click(). Noted that I'm using this in a single select listbox and not a multi select listbox. You can use an If statement with a boolean to apply it to multiselect. Goodluck!

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As 

Integer, ByVal x As Single, ByVal Y As Single)

    On Error Resume Next  'You can comment this out for trouble shooting
    If Button = 1 And UBound(ListBox1.List) <> -1 Then
        ListBox1.Selected(((Y / 9.75) - 0.5) + ListBox1.TopIndex) = True
        MsgBox "left Click"
        'You can use Button = 2 for right click
        'Do some other stuff including listbox1.select(1234)

    End If
End Sub

Comments

[user555265]

Thanks for the info - in fact we did implement the first solution as a work around, my question was more why is there a difference in behaviour for the two cases I described

[user555265]

I believe we did try with ListBox Click but i think that this triggered before the selection was "physically" selected in the list box, so changing the Selected property had no effect

[Gregg Burns]

Thanks. I believed this wouldn't add value, but it can be leveraged because the selection is already registered prior to the event firing. Therefore you can detect a change to a specific list item in some cases. For instance, I wanted a list item (0) to 'Reset Filters' below it, it can work in this case because I always clear it. If it's selected, then I know user just did it and I can follow suit.