vba listbox event fires twice

Question

I am trying to get this to work in excel userforms. When selecting a value in the listbox its change event is called twice. Unable to get around it even after putting a flag in place. Not sure why the change event is called twice. After googling it seams like when the control gets focus the change event is called. Below is the code.

Public eventsOFF As Boolean
Public ctr As Integer

Private Sub ListBox1_Change()
Dim tmp As String, sel As Variant, s As Variant

If eventsOFF Then Exit Sub

eventsOFF = True
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then tmp = tmp & ListBox1.List(i) & ","
Next

ListBox1.Clear
sel = Split(tmp, ",")

ListBox1.AddItem "Entry 1"
ListBox1.AddItem "Entry 2"
ListBox1.AddItem "Entry 3"
ListBox1.AddItem "Entry 4"
ListBox1.AddItem "Entry 5"

For i = 0 To ListBox1.ListCount - 1
For Each s In sel
If s = ListBox1.List(i) Then ListBox1.Selected(i) = True
Next
Next
eventsOFF = False
ctr = ctr + 1
Debug.Print ctr
End Sub

Private Sub UserForm_Initialize()
ListBox1.AddItem "Entry 1"
ListBox1.AddItem "Entry 2"
ListBox1.AddItem "Entry 3"
ListBox1.AddItem "Entry 4"
ListBox1.AddItem "Entry 5"
End Sub

Programmatically selecting a value will fire the change event (Then ListBox1.Selected(i) = True). I'm not sure how to get around it. — Crowcoder
The eventsOFF flag takes care of that. The funny thing is when I step through the code in debug mode it works fine ! Where can I find the events hierarchy for the listbox? — illoosions
I don't think there are many hooks into events from VBA. This is not a typical thing you are trying to do with a listbox. — Crowcoder
I have spent sometime finding way to retain listbox selected items when tab into other controls and back. Then I found when the ListBox is first Clicked or tab into, it's the _Enter() event. With similar logic you have here (variable scope is different), you can retain those selected items when tabbing around the controls within the userform. — PatricK
ListBox has problems with Click, Changed, and MouseDown events. Sometimes the event is triggered twice, sometimes the VBA does not respond for a few seconds. The SetFocus solution from Jyhren will help partially eliminate the double capture of the event. However, if the application stops or even freezes, it is more appropriate to use the MouseUp event that does not show the problem. — Fox

Jyhren Jyhren · Accepted Answer · 2016-07-14T18:16:27

Under ListBox1_MouseMove event insert the following line will solve your problem

ListBox1.SetFocus

vba listbox event fires twice

1 Answers