VBA (Excel) ActiveX ListBox Changing the recursive behavior of events

Question

I am not a VBA programmer, so I apologize in advance if some of my terminology in this matter is incorrect. My colleague wanted to clear the selection from the list as soon as it was selected. After some searches, we found that one way to do this was through the Change event. First 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 raises the Change event in the list, and it actually becomes an infinite loop and causes Excel (2007) to crash. Given that we knew there were two entries, we also tried:

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

And it works! Although we expect the same behavior - to set the Selected property, to raise the Change event to fire again and get an infinite loop.

However, it seems that once, for example, ListBox1.Selected (0) = False, the change event is restarted, but in this iteration it does not restart this line - I think because it knows that this selected for this element is already set to False , therefore nothing changes.

But if that is the case, we also expect this behavior in the first solution .. so it seems that there is some difference in saying ListBox1.Selected (i) = False compared to indicating the actual index of the element directly (and not through the variable i ).

Does anyone know the reason for this behavior? Hope the question makes sense, I tried to explain it as best as possible.

Thanks Amit

Answer 1

I'm late for the party, but hope this helps others. I had a problem with Listbox1_Click () infinite loop, not change () . However, I think this may be a viable solution for both.

Whenever I called Listbox1.Selected (i) = True , it would run it as Click () or Change () . In my click () routine, there is a specific index that will force the whole list to re-populate itself with a new list and reselect . This causes an endless loop when it re-selects itself. It took me a day to troubleshoot, but in the end, the solution was not to use the click () event; instead, I used the MouseDown () event with a little calculation. This eliminates the use of click () . It is noted that I use this in one select list, and not in a select list of several items. You can use the If statement with a boolean to apply it to the multi selector. Good luck!

 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 2

Attach your loop to a click event instead of a change event to stop endless 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 that the "why" of this question has an easy answer. Typically, a word is very linear and can only process one thing at a time. However, modern processors optimize code and manage things in tandem. The word is simply confused in fairly predictable ways of providing relatively consistent workarounds for confusion. The trick you mentioned above (twice) is one of the popular ways to solve this problem (I saw a lot of recommendations when I had the same problem with lists).

I prefer to improve logic, and then try to work carelessly. As above, instead of disabling events or using a double trick, I just realized that the change event is not the right event to use.

Answer 3

I would have to do more research on this, but I know that during the For...Next loop, when the code falls into the Next line, it adds 1 to the variable (if you did not define a Step ), and then the logical a test to determine if a variable is saved between your included For blah conditions. Since each of them runs independently, it allows the code to check your _Change() event. Some great workarounds to prevent an infinite loop: Edit () Event Endless loops

EDIT: Below is more information about what triggers the ListBox.Change Documentation trigger. He mentions that

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

which can be checked between cycles.

Answer 4

You are correct in using the Change event, but it is somehow complicated in ListBoxes. You obviously fire the Change event every time you deselect an item because you are changing the ListBox from the code.

The answer from guitarthrower goes in a good direction, but I don't think Application.EnableEvents would do the job in this case, since it does not affect ActiveX objects.

So, I would try these two alternatives that try to emulate the operation of Application.EnableEvents simple programming:

• Alternative 1: general solution for the Change event. However, this is a little risky if the NoExecute variable NoExecute not reset for any reason (the function exits 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: the best solution in this case, although not as beautiful 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 how it works.

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

Answer 5

To stop the endless frenzy, you can do this (an even more complicated question: why do you do it anyway, because after that no value can be selected or can actually be, but it immediately changed 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 cause of the infinite loop is not visible, and you can only guess, but first correct the assumption that the block of code without the next loop will not cause the same problem. This actually does the same as the first, and gets stuck in an infinite loop if you change the value of ListBox1.Selected (1) or later.

Why this happens, this change event will be triggered immediately after the first value changes, and as the first cell changes, the change event is triggered and the code execution stops for this instance and starts again. Now both codes work without causing a loop if only the first element is changed and why this happens, probably for a compiler that uses automation. I can’t tell you the real reason for the events, but it’s probably that the compiler predicts when only the first value changes, and optimizes the code to make only one change, and nothing else, and when other values ​​change, it tries to rewrite each value and the first value each time and again when an infinite loop starts.

Here's how to do it to understand what is happening under the hood, you better contact MS.

Answer 6

For future reference only: if you just want to turn off the selection in the ListBox control, you can either use

 ListBox.Enabled = False 

or

 ListBox.Locked = True 

or both, of course.

You can find information about differences in MSDN behavior.
Basically it should be like this (directly from the related MSDN article above):

• If Enabled and Locked are True , the control can receive focus and is usually displayed (not darkened) in the form. The user can copy but not edit the data in the control.
• If Enabled True and Locked False , the control can receive focus and is usually displayed on the form. The user can copy and edit data in the control.
• If Enabled False and Locked True , the control cannot receive focus and dims in the form. The user can neither copy nor edit data in the control.
• If Enabled and Locked both False , the control cannot receive focus and dims in the form. The user can neither copy nor edit data in the control.

Note that I'm not sure about the behavior in Office 2007, since I tried your example with Office 2013 and got the same behavior for both code snippets. And both of them do not prevent the selection, but rather “nothing” (well, they are called twice, but the change does not affect the displayed selection).

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