none
Problem with VBA listbox event design RRS feed

  • Question

  • I have an Excel VBA userform with two multiselect listboxes that refer to the same data set.

    What I want is for the user to select items in each box and the selections to be mutually exclusive.

    The main code happens after selections are done and they click a command button, but I want the mutual exclusivity to happen on the fly via listbox events.  (The main code will create several series collections and plot them on a dual axis chart).

    I think I'm getting into an infinite loop using change events on each listbox and am looking for a simple way to write the code.  The actual error message is: -2147417848 : The object invoked has disconnected from its clients

    I thought about toggling Application.EnableEvents while the OnChange macro is executing, but I haven't found a way to make that work yet either.  Here is my current (flawed) code:

    Private Sub ListBox1_Change()
        'If item is selected for primary axis, make sure it is not selected for secondary axis
        With Me.ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) Then Me.ListBox2.Selected(i) = False
            Next i
        End With
    End Sub

    Private Sub ListBox2_Change()
        'If item is selected for secondary axis, make sure it is not selected for primary axis
        With Me.ListBox2
            For i = 0 To .ListCount - 1
                If .Selected(i) Then Me.ListBox1.Selected(i) = False
            Next i
        End With
    End Sub

    Thursday, July 31, 2014 10:40 PM

Answers

  • There are probably more elegant ways to do this, but one approach would be to use a boolean flag and only execute your change code based on that flag, something like the aircode below. Do the same on the other listbox change event. That way, during the loop the boolean is false, and the other listbox won't try to change the calling listbox because the change event will functionally do nothing during the originating listbox change execution. The boolean needs to retain value across Subs, so you will want to DIM it at the top of a module

    EDIT: gotta put the changeback to TRUE on the inside of the loop, I wasn't thinking...

    Private Sub ListBox1_Change()

    'By default, have this boolean set to true, maybe as part of your form initialization

    If ExecuteMyChange = True then

         ExecuteMyChange=False

        'If item is selected for primary axis, make sure it is not selected for secondary axis
        With Me.ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) Then Me.ListBox2.Selected(i) = False
            Next i
        End With

    ExecuteMyChange= True 

    Endif

     

    End Sub


    • Edited by Keith Ruck Thursday, July 31, 2014 11:12 PM
    • Proposed as answer by George HuaModerator Friday, August 1, 2014 9:18 AM
    • Marked as answer by Pete Kies Friday, August 1, 2014 12:36 PM
    Thursday, July 31, 2014 11:04 PM

All replies

  • There are probably more elegant ways to do this, but one approach would be to use a boolean flag and only execute your change code based on that flag, something like the aircode below. Do the same on the other listbox change event. That way, during the loop the boolean is false, and the other listbox won't try to change the calling listbox because the change event will functionally do nothing during the originating listbox change execution. The boolean needs to retain value across Subs, so you will want to DIM it at the top of a module

    EDIT: gotta put the changeback to TRUE on the inside of the loop, I wasn't thinking...

    Private Sub ListBox1_Change()

    'By default, have this boolean set to true, maybe as part of your form initialization

    If ExecuteMyChange = True then

         ExecuteMyChange=False

        'If item is selected for primary axis, make sure it is not selected for secondary axis
        With Me.ListBox1
            For i = 0 To .ListCount - 1
                If .Selected(i) Then Me.ListBox2.Selected(i) = False
            Next i
        End With

    ExecuteMyChange= True 

    Endif

     

    End Sub


    • Edited by Keith Ruck Thursday, July 31, 2014 11:12 PM
    • Proposed as answer by George HuaModerator Friday, August 1, 2014 9:18 AM
    • Marked as answer by Pete Kies Friday, August 1, 2014 12:36 PM
    Thursday, July 31, 2014 11:04 PM
  • Thanks!  I saw something like that elsewhere and I get the gist of it so I can give it a try.

    As the code always affects the opposite listbox, I was wondering if there was a way to tell which listbox had the focus and use that as an opt-out in the code.  Or if there were some other listbox events I should be looking at instead of the change event.

    Friday, August 1, 2014 12:02 AM