VBA (Excel) ActiveX ListBox Change Event recursive

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

Answer 1

I would have to do more research on this, but I do know that during a For...Next loop, once the code hits the Next line it adds 1 to the variable (assuming you haven't defined a Step) and then runs a logic test to determine if the variable is still between your inclusive For blah conditions. Because each of these runs independently, it allows the code to check for your _Change() event. Some great workarounds for preventing an infinite loop: Change() Event Infinite Loops

EDIT: Here is some additional information on what causes the trigger ListBox.Change Documentation. It mentions that

Also, a programmatic change to ListBox.ListIndex also triggers the Change event.

which might be what is being checked between loops.

Answer 2

Just for future reference: If you just want to disable selection in a ListBox control you can either use

ListBox.Enabled = False

or

ListBox.Locked = True

or both, of course.

You can find details on the difference in behaviour on MSDN.
Basically it's supposed to be like this (directly from the linked MSDN article above):

• If Enabled and Locked are both True, the control can receive focus and appears normally (not dimmed) in the form. The user can copy, but not edit, data in the control.
• If Enabled is True and Locked is False, the control can receive focus and appears normally in the form. The user can copy and edit data in the control.
• If Enabled is False and Locked is True, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.
• If Enabled and Locked are both False, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.

Note that I'm not sure about the behaviour in Office 2007 since I tried your example with Office 2013 and get the exact same behaviour for both code snippets. And they both don't prevent the selection but rather do "nothing" (well, they get called two times but the change doesn't affect the displayed selection).

And during my experiments I also noticed, that the ListBox_Change event gets called before the ListBox_Click event.

Answer 3

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

Answer 4

You are right about using the Change event, but it's somehow tricky in ListBoxes. You're obviously triggering the Change event every time you deselect an element, because you are changing the ListBox from code.

The answer from guitarthrower goes in the good direction, but I don't think that Application.EnableEvents would do the job in this case as it does not have any effect on ActiveX objects.

So, I would try this two alternatives which try to emulate the job of Application.EnableEvents by plain programming:

• Alternative 1: general solution for the Change event. However, it's a bit risky if the NoExecute variable is not reseted by any reason (function exiting due to an error).

  Private NoExecute As Boolean
  
  Private Sub ListBox1_Change()
  
      If NoExecute Then Exit Sub
  
      NoExecute = True
  
      For i = 0 To ListBox1.ListCount - 1
          ListBox1.Selected(i) = False
      Next
  
      NoExecute = False
  
  End Sub
  

• Alternative 2: best solution in this case, although not as pretty as the previous one.

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

Let me know wether it worked.

This site describes Alternative 1 in depth: http://www.cpearson.com/excel/SuppressChangeInForms.htm

Answer 5

To stop the infinite insanity, you can do this (tough even better question is, why would you do this anyway, because after this no value can be selected or actually can be, but it's immediately changed back to false) -

Private Sub ListBox1_Change()
    For i = 0 To ListBox1.ListCount - 1 
         if ListBox1.Selected(i) = true then ListBox1.Selected(i) = false 
    Next i
End Sub

Now the reason for the infinite loop is not visible and one can only guess, but first lets correct the assumption that the code block without the for next loop would not cause the same issue. It actually does the same thing as the first one and get stuck in an infinite loop, if you change the value of the ListBox1.Selected(1) or later..

Why this happens is the change event will be triggered right after the first value is changed, and as the first cell is changed the change event gets triggered and code execution is stopped for that instance and begins again. Now both of the codes work without causing a loop if only the first item is changed and why this happens is probably for the compiler kicks in with it's automatics. I can't tell you the real reason behind the happenings, but what probably happens is the compiler predicts when only the first value is changed and optimizes the code to do only the one change and nothing else and when other values change it is trying to rewrite every value and the first value every time and again the infinite loop is triggered.

That's just how it is, to get a full understand what is happening under the hood, you'd better contact MS.

Answer 6

Attach your loop to the click event instead of the change event to stop the infinite loops:

Private Sub ListBox1_Click()
'Do the stuff you want to do based on the selection

'Clear selections
For i = 0 To ListBox1.ListCount - 1
    ListBox1.Selected(i) = False
Next i
End Sub

I do not believe the "why" of this question has an easy answer. Generally, word is very linear and can only process 1 thing at a time. Modern processors however, optimize code and run things in tandem. Word simply gets confused in predictable enough ways to provide relatively consistent workarounds for the confusion. The trick you mentioned above (doing it twice) is one popular way to handle this issue (I saw many reccomendations back when I had the same issue with list boxes).

I prefer to enhance the logic rather then attempt a sloppy work around. As above, rather then turn off events or use the double trick, I simply realized that the change event was not the correct event to use.