none
Listbox Change event triggers twice RRS feed

  • Question

  • I have 2 combo boxes and 5 listboxes on a userform. In the full program, users make their selections starting with the combo boxes and then the list boxes.  Each time a selection is made, the next Listbox is filled with appropriate data.  This all works great until users started complaining that they have to click twice to highlight their selection.<o:p></o:p>

    I first thought it might be a simple logical error, but after I stripped down the code to barely anything, and then stepping thru it, I noticed that after the ListBox4_Change event has completed, it starts again, but this time with no selections.  For the user it looks like the selection he made didn't register, but it actually ran the code twice (first getting all the data and then removing it again). The strange thing is that after the users had their "miss click" and they attempt it again, everything works 100%.<o:p></o:p>

    I am new to this forum and do not know what the rules are regarding posting code (or length of messages), but will gladly supply the stripped down code or attach the userform if possible.<o:p></o:p>

    I am using Excel 2010 (Version:14.0.6129.5000 32-bit) running on Windows 7. The Listboxes
    have MultiSelect = True. For the rest everything is standard.<o:p></o:p>

    Any assistance will be greatly appreciated.

    Friday, May 17, 2013 12:48 PM

Answers

  • I see what you mean, tricky! What appears to be happening is the change event fires twice if on the first click the listbox was not the activecontrol. That explains why your mousemove and setfocus fixes, though that might put the control in focus when inadvertently moving the mouse over the listbox.

    In a light test this seems to work, though it may or may not skip a change event you want to get called because code in another event changes stuff. If so change the static bExit to a module level mbExit and temporarily set the flag to skip the ResetListBox function

    Private Sub ListBox1_Change()
         If ResetListBox(ListBox1) Then
                 Exit Sub
         End If
    
    ' similar for ListBox2 etc
    
    Private Function ResetListBox(lbx As MSForms.ListBox) As Boolean
    Dim x As Long
    Static bExit As Boolean
         If Not bExit Then
                 x = lbx.ListIndex
                 If x >= 0 And Not lbx Is Me.ActiveControl Then
                         bExit = True
                         lbx.Selected(x) = Not lbx.Selected(x)
                         bExit = False
                         ResetListBox = True
                 End If
         End If
    End Function

    Peter Thornton

    • Marked as answer by Miela S Wednesday, May 21, 2014 9:20 AM
    Tuesday, May 21, 2013 10:16 AM
    Moderator

All replies

  • You should post your code, which is encouraged here.  Include, at least, the code for listbox4.
    Friday, May 17, 2013 1:03 PM
  • Please forgive my use of default names, but I recreated it just to test it.  Here is the code as requested.  As you can see it isn't anything special and looks very similar to the other Listbox's code. 

    Private Sub ComboBox1_Change()
        ComboBox2.Clear
        Select Case ComboBox1
            Case "My Data 1"
                ComboBox2.AddItem "My Other Data 1"
                ComboBox2.AddItem "My Other Data 2"
            Case "My Data 2"
                ComboBox2.AddItem "My Other Data 1"
            Case "My Data 3"
                ComboBox2.AddItem "My Other Data 1"
                ComboBox2.AddItem "My Other Data 2"
                ComboBox2.AddItem "My Other Data 3"
        End Select
        ComboBox2.ListIndex = 0
        ListBox2.Clear
        ListBox3.Clear
        ListBox4.Clear
        ListBox5.Clear
    End Sub

    Private Sub ComboBox2_Change()
            ListBox1.AddItem "My Value 1"
            ListBox1.AddItem "My Value 2"
            ListBox1.AddItem "My Value 3"
            ListBox2.Clear
            ListBox3.Clear
            ListBox4.Clear
            ListBox5.Clear
    End Sub
    Private Sub ListBox1_Change()
            ListBox2.Clear
            SomethingSelected = False
            For MyLoop = 1 To ListBox1.ListCount
                If ListBox1.Selected(MyLoop - 1) = True Then
                    SomethingSelected = True
                    Exit For
                End If
            Next
            If SomethingSelected Then
                ListBox2.AddItem "My Value 1"
                ListBox2.AddItem "My Value 2"
                ListBox2.AddItem "My Value 3"
                ListBox2.AddItem "My Value 4"
                ListBox2.AddItem "My Value 5"
            End If
                ListBox3.Clear
                ListBox4.Clear
                ListBox5.Clear
    End Sub
    Private Sub ListBox2_Change()
            SomethingSelected = False
            For MyLoop = 1 To ListBox2.ListCount
                If ListBox2.Selected(MyLoop - 1) = True Then
                    SomethingSelected = True
                    Exit For
                End If
            Next
            ListBox3.Clear
            If SomethingSelected Then
                ListBox3.AddItem "Another Value 1"
                ListBox3.AddItem "Another Value 2"
                ListBox3.AddItem "Another Value 3"
                ListBox3.AddItem "Another Value 4"
                ListBox3.AddItem "Another Value 5"
            End If
            ListBox4.Clear
            ListBox5.Clear
    End Sub

    Private Sub ListBox3_Change()
            SomethingSelected = False
            For MyLoop = 1 To ListBox3.ListCount
                If ListBox3.Selected(MyLoop - 1) = True Then
                    SomethingSelected = True
                    Exit For
                End If
            Next
            ListBox4.Clear
            If SomethingSelected Then
                ListBox4.AddItem "Value 1"
                ListBox4.AddItem "Value 2"
                ListBox4.AddItem "Value 3"
                ListBox4.AddItem "Value 4"
                ListBox4.AddItem "Value 5"
            End If
            ListBox5.Clear
    End Sub

    Private Sub ListBox4_Change()
            ListBox5.Clear
            SomethingSelected = False
            For MyLoop = 1 To ListBox4.ListCount
                If ListBox4.Selected(MyLoop - 1) = True Then
                    SomethingSelected = True
                    Exit For
                End If
            Next
            If SomethingSelected Then
                ListBox5.AddItem "My Item 1"
                ListBox5.AddItem "My Item 2"
                ListBox5.AddItem "My Item 3"
                ListBox5.AddItem "My Item 4"
                ListBox5.AddItem "My Item 5"
                ListBox5.AddItem "My Item 6"
                ListBox5.AddItem "My Item 7"
            End If
    End Sub

    Private Sub UserForm_Initialize()
        ComboBox1.AddItem "My Data 1"
        ComboBox1.AddItem "My Data 2"
        ComboBox1.AddItem "My Data 3"
    End Sub


    • Edited by Miela S Friday, May 17, 2013 1:25 PM
    Friday, May 17, 2013 1:10 PM
  • If you add this as the first line of every sub:

    Application.EnableEvents = False

    and then as the last line, before End Sub:

    Application.EnableEvents = True

    That should fix it - the interdependencies are hard to follow, but clearly one event is clearing your other selection when you don't want it to...


    Friday, May 17, 2013 3:23 PM
  • ' in the declarations area at the top of the userform module
    Private mbExit As Boolean
    
    Private Sub ListBox3_Change()
    If mbExit Then
         Exit Sub
    Else
         mbExit = True
    End If
    
    '    code to change other listboxes
    
    mbExit = False
    End Sub

    Include similar in your other listbox change events, or maybe only one of the If clauses depending on exactly what you want to occur and or prevent

    Peter Thornton

    Friday, May 17, 2013 3:36 PM
    Moderator
  • I've added the lines of code as suggested.  The only difference is that the ListBox3_Change event is now being called twice.

    However - I did noticed something else after removing the lines again.  If you use the mouse to click on the listbox, this happens - but - If you use your keyboard (Tab and space) to select an entry, then the change event occurs only once.

    Monday, May 20, 2013 7:14 AM
  • Thanks for the suggestion, however I had these lines in already and removed it to simplify the code for posting. 

    If you see my reply to Bernie's suggestion, you will see that my problem is only when the user is using the mouse, but with the keyboard it works fine.  I thought the event would work the same no matter what input device are used. 

    Monday, May 20, 2013 7:21 AM
  • Hi All - I think I got the solution!  I added the following for all listboxes and it seems to be working now:

    Private Sub ListBox1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        ListBox1.SetFocus
    End Sub

    The only question that remains is why this is necessary to add? 

    Monday, May 20, 2013 7:27 AM
  • Thanks for the suggestion, however I had these lines in already and removed it to simplify the code for posting.

    It might simply for posting but ommiting essentials like that makes it difficult for people to respond usefully. Normally setting a flag to exit events in this way is the solution.

    Concerning your adjacent post that setfocus solves your problem again suggests there other relavant aspects that you have not shown us, so without knowing what you are doing it's not possible to suggest why that helps. At face value though it shouldn't make any difference at all.

    Peter Thornton

    Monday, May 20, 2013 8:49 AM
    Moderator
  • My apologies if I created the idea of not giving all information.  That is why I posted the full code of my test document.  The full program makes use of a global check to see if updating/events is already happening.  As the problem persisted in the test file after removing it, I did not see this as being part of the problem.

    The fact that this problem only occurred when using a mouse was discovered using the test file and I eagerly reported this in my reply.  I still have no idea why I need to use the setfocus and was hoping that someone could help me understand this behaviour.

    Peter, I fully agree with your statement that it shouldn’t make any difference at all.  The reality unfortunately is that it does.

    Thanks for your help. I greatly appreciate it.
    Kind Regards

    Monday, May 20, 2013 10:04 AM
  • But your test code as posted gives no indication as to why your listbox change events are firing (other than when user changes something) yet alone why the SetFocus fixes, which presumably means there's other stuff going on that you haven't told us about.

    I tried your code and it all works fine, nothing in it that makes other change events fire.

    Couple of things in passing:

    - Disabling application events has no effect on userform controls' events

    - instead of that If ListBox3.Selected loop, just check the ListIndex value, if it's -1 it means nothing is selected.

    Peter Thornton

    Monday, May 20, 2013 10:59 AM
    Moderator
  • Hi Peter,

    I'm using the Loop for the Listboxes because they have MultiSelect = True.  So in the real program I Loop thru all the items and then create the next listbox according to the data selected.

    What worries me is that you had no problem in executing the code.  I've tested this on several of our computers and they all give me the same problem.  Could it be something in our setup? 

    To clear up any confusion, will it be possible for me to mail you my test document?  I will remove the setfocus lines to create the origenal "miss-click".

    Kind Regards

    Monday, May 20, 2013 12:27 PM
  • You can upload the test file to any file sharing site and post the link here.
    Monday, May 20, 2013 2:57 PM
  • As Bernie suggested upload to a file sharing site. If possible try www.skydrive.com as downloading from quite a few sites get blocked by AV apps

    Send the file with 2 userforms, one demonstrating the problem (be sure to clearly explaine how to repro the problem). Include a similar form with the SetFocus that apparently fixes (drag the form into a different project, rename the form, and drag it back again)

    Peter Thornton


    Monday, May 20, 2013 3:39 PM
    Moderator
  • Thanks again for your help.  I uploaded the file and tried to explain the best I can.  I truly hope that it works this time (or doesn't work)... :-)

    skydrive.live.com/redir?resid=9ED2090EF358A72A!107&authkey=!AJWLKmeeZr5qMDc

    Apparently I am not allowed to post links till my account is verified.  The above is an https site.

    Kind Regards

    Tuesday, May 21, 2013 6:50 AM
  • I see what you mean, tricky! What appears to be happening is the change event fires twice if on the first click the listbox was not the activecontrol. That explains why your mousemove and setfocus fixes, though that might put the control in focus when inadvertently moving the mouse over the listbox.

    In a light test this seems to work, though it may or may not skip a change event you want to get called because code in another event changes stuff. If so change the static bExit to a module level mbExit and temporarily set the flag to skip the ResetListBox function

    Private Sub ListBox1_Change()
         If ResetListBox(ListBox1) Then
                 Exit Sub
         End If
    
    ' similar for ListBox2 etc
    
    Private Function ResetListBox(lbx As MSForms.ListBox) As Boolean
    Dim x As Long
    Static bExit As Boolean
         If Not bExit Then
                 x = lbx.ListIndex
                 If x >= 0 And Not lbx Is Me.ActiveControl Then
                         bExit = True
                         lbx.Selected(x) = Not lbx.Selected(x)
                         bExit = False
                         ResetListBox = True
                 End If
         End If
    End Function

    Peter Thornton

    • Marked as answer by Miela S Wednesday, May 21, 2014 9:20 AM
    Tuesday, May 21, 2013 10:16 AM
    Moderator
  • Peter,

    I'm so glad you could experience this strange event occurrence. I was starting to think it was our system!

    I am extremely happy with your proposed solution. It traps the execution of the event a second time perfectly and as you suggested it should be more stable than the "MoveMouse" trick I tried.

    I still don't know why the event fires twice. Maybe it’s a bug with listboxes or something, but at least my users should be happy after I implement your proposal into the original program.

    Kind Regards

    Tuesday, May 21, 2013 1:26 PM
  • Hola!

    Yo tuve la misma situación pero con el evento Click, para resolverlo tuve que implementar el siguiente truco:

    Public Intento as integer

    intento = 1

    LB_Click(...)

    if intento = 1 then

    'ejecuta tu código...

    intento = 2

    else

    intento = 1

    end if

    De esta manera la segunda llamada sólo inicializa la variable.

    Saludos!

    PD

    Se que ya lo resolviste pero lo dejo para la comunidad como una alternativa.

    Friday, February 14, 2014 5:03 AM
  • I have experienced a very similar problem on a listbox control on a form in Outlook 2013 VBA. The form used to work "correctly" then after some edit that I thought were unrelated, it started requiring that the user click twice to activate the event. The code I changed should not have altered the functioning of this listbox. 

    What I discovered is that I could restore the correct functioning by opening the form in the edit window. Move the listbox on the form, hit undo to restore the previous location and then save and restart the form. It then worked correctly with a single click. No idea why this would work as nothing in the code was changed.

    This solution has now worked for me on two different list boxes on the same form and then again about a month later when one of the same listboxes started doing the same thing again after some other minor edits.

    • Proposed as answer by sjpeer Thursday, June 9, 2016 1:05 PM
    Monday, September 28, 2015 7:54 PM
  • For whatever reason, I am exactly the same problem. It occurs to me in all my forms, sometimes in works, sometimes not. It seems, that it has to do with the setting of the user forms or any other reasons.

    I only wonder: I have the problem several times already and I am sure, that there must be a general issue with that.

    I have the problem, that I am working with RowSource data and I am update the EXCEL Spreadsheet reference. 

    To have a semiphore at the beginning of the subroutine works for me, but my display for the list will not update. .. but I guess, that should be another thread in this forum...

    So, if anybody has a better idea, WHY there are two events sometimes ... highly appreciated.

    Micha

    Wednesday, April 6, 2016 5:30 AM
  • I had an identical problem with an excel listbox in both Excel 2000 and Excel 2013.  The solution proposed by DAL821 worked perfectly!  Thanks!!!!

    Thursday, June 9, 2016 1:12 PM