Sorting Multiple Listboxes With Lists That Are Dependent Upon Each Other

[CaliKidd]

Imagine a userform with 3 listboxes (lbAnswer1, lbAnswer2, lbAnswer3). When the form is initialized, all of the listboxes are populated with the letters of the alphabet, like this:

With lbAnswer1
[INDENT].addItem "A"
.addItem "B"
.addItem "C"
...etc
.addItem "Z"
[/INDENT]End With
With lbAnswer2
[INDENT].addItem "A"
.addItem "B"
.addItem "C"
...etc
.addItem "Z"
[/INDENT]End With
With lbAnswer3
[INDENT].addItem "A"
.addItem "B"
.addItem "C"
...etc
.addItem "Z"
[/INDENT]End With

OK, now let me describe my objective conceptually.

1. When the form is run, I want the user to be able to click the dropdown on each listbox and choose a letter. I want that selected letter to be removed from the other listboxes so it cannot be chosen again (i.e., no duplicates). For example, if the user selects the letter "M" on the second listbox, the letter "M" should be removed from the list of selectable entries from the first and third listboxes.

2. However, if the user changes his mind and decides to choose the letter "K" instead of "M", the letter "K" needs to be removed from the other lists and the previously selected letter "M" needs to be added back into the lists.

3. Additionally, the lists need to be alphabetically sorted so the newly added letter "M" does not appear at the end of the list, but rather where it should belong alphabetically.

4. Finally, the user should be able to select the listboxes in any order, not be required to select them sequentially. In other words, they should not have to select the first listbox, then the second, then the third, although they should be free to go in this or any other order they choose.

I hope that is clear.

OK, so I'm wondering what the VBA code would look like to accomplish this. I've been struggling with this all day and haven't found a solution in any of the forums.

Any ideas?

 

[CaliKidd]

Bump. Help, please.

 

[CaliKidd]

Would appreciate any help, even if it only addresses some of my requests. Thanks in advance.

 

[MickG]

You can do this with a Class Module, which requires a bit of setting up from you.
If your interested.
Mick

 

[CaliKidd]

Mick, thanks for your reply. Yes, I would be interested in developing the class module, if that's what it takes.

However, I was hoping it would be something more along the lines of putting the code in the various listbox_change() subs where:

- the user's selection, "M", is removed from the all of the listbox lists using .removeitem
- if the user changes his selection to "K", then it would remove "K" from the all of the listbox lists using .removeitem and add back "M" using .additem.
- the listbox contents would then be dumped into an array, sorted alphabetically, then added back using .additem (because there is no Sort method for a VBA listbox!)
- the aforementioned steps would work regardless of the order the user selects the listboxes.

That's my conceptual idea any way , but my VBA skills are not up to par to actually write the code.

Can you help get me started and/or explain why I would need a class module to do this? It will help me improve my VBA knowledge.

P.S. One more thing to make my request more clear. By way of analogy, think about when you use the setdrive command to assign a drive letter to a drive. Once you've made the letter assignment, that letter is not available for assignment to other available drives. That's analogous to what I am trying to do, although this has nothing to do with drives.

 

[MickG]

Hi, The bit of code I written First fills each list box with the "Alphabet" when the userform is "Initialised", then when you click an Item from any one List box the Class module refills the other 2 listboxes with the same "Alphabet" excluding the Selection Letter.
I suppose you could produce the same result by calling a sub from each List box, but I thought the Class was more interesting.
Does that sound like the sort of thing you want
I wasn't sure whether the "Alphabet" was just for your example.
Regards Mick

 

[CaliKidd]

Hi Mick,
Yes, that's basically the idea -- multiple listboxes working in tandem, so that the selection on one listbox affects the available selections of the others. I tried to write the code on my own before posting this request, calling a sub from each listbox, but I couldn't get it to work properly. The main problem was when changing the selection(s). For example, if the first listbox selected was "A", the second "Z" and the third "J", and then I decided to change the selections, the listboxes didn't get repopulated correctly. I think the listindexes were throwing me off, but I couldn't figure out why.

In any case, while I initiated this post by providing a basic example, my actual form does contain listboxes (6, actually) that use the "alphabet" as a selection list. I assumed that if I could figure out how to make it work with 3 listboxes, then I could scale it up to 6. So, if you have some code you're willing to share, I can look into the logic you used, and hopefully tailor it to my needs.

I've not advanced to the point where I've ever used Class Modules (yet), so this may be a good entry point to strengthen my VBA skills. I'm only at the skill level where I can write code to get things done thru brute force. It's usually long, redundant, and inefficient, and I know enough now to know there are better ways of getting things done, so I am always motivated to learn better ways of programming (i.e., working smarter, rather than harder).

Thanks in advance for taking the time to help. ray:

 

[MickG]

I've now written the code for a basic module, shown below, and if at any time you want the "Class" let me know.
Paste the code below into the Useform Module.
I've added the Click Event for all 6 Listboxes.
To run code just select any item in any listbox.

Private [COLOR="Navy"]Sub[/COLOR] ListBox1_Click()
Call Remlets(ListBox1, ListBox1.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ListBox2_Click()
Call Remlets(ListBox2, ListBox2.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ListBox3_Click()
Call Remlets(ListBox3, ListBox3.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ListBox4_Click()
Call Remlets(ListBox4, ListBox4.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ListBox5_Click()
Call Remlets(ListBox5, ListBox5.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ListBox6_Click()
Call Remlets(ListBox6, ListBox6.value)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Ray(1 To 26)
[COLOR="Navy"]Dim[/COLOR] lbox [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]For[/COLOR] n = 65 To 90
        Ray(n - 64) = Chr(n)
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] lbox [COLOR="Navy"]In[/COLOR] Me.Controls
       [COLOR="Navy"]If[/COLOR] TypeName(lbox) = "ListBox" [COLOR="Navy"]Then[/COLOR]
            lbox.List = Application.Transpose(Ray)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] lbox
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Paste this code into a Basic Module:-

[COLOR="Navy"]Sub[/COLOR] Remlets(LB [COLOR="Navy"]As[/COLOR] Control, tom [COLOR="Navy"]As[/COLOR] String)
[COLOR="Navy"]Dim[/COLOR] Ray(1 To 26)
[COLOR="Navy"]Dim[/COLOR] lbox [COLOR="Navy"]As[/COLOR] Control
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]For[/COLOR] n = 65 To 90
     [COLOR="Navy"]If[/COLOR] Not Chr(n) = tom [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Ray(c) = Chr(n)
    [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] lbox [COLOR="Navy"]In[/COLOR] LB.Parent.Controls
      [COLOR="Navy"]If[/COLOR] TypeName(lbox) = "ListBox" And Not lbox.Name = LB.Name [COLOR="Navy"]Then[/COLOR]
            lbox.Clear
            lbox.List = Application.Transpose(Ray)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] lbox
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mikerickson]

Perhaps something like this in the userform's code module.
The basic logic for filling one ListBox is

-Save current selected value in .Tag
-Clear the List in question

-Loop through every possible list item (Chr(65)-Chr(90 in this example, but it could be an fixed array)
--If the possible item is not selected by other list boxes, add it to the ListBox in question.
--If the newly added item = .Tag (i.e. was selected at start) note the ListIndex
-End loop

-Select the noted (.Taged, original) value from the list.

Dim ufEventsDisabled As Boolean

Private Sub ListBox1_Change()
    If ufEventsDisabled Then Exit Sub
    Call FillWhileChecking(ListBox2)
    Call FillWhileChecking(ListBox3)
End Sub

Private Sub ListBox2_Change()
    If ufEventsDisabled Then Exit Sub
    Call FillWhileChecking(ListBox1)
    Call FillWhileChecking(ListBox3)
End Sub

Private Sub ListBox3_Change()
    If ufEventsDisabled Then Exit Sub
    Call FillWhileChecking(ListBox1)
    Call FillWhileChecking(ListBox2)
End Sub

Sub FillWhileChecking(aListbox As MSForms.ListBox)
    Dim i As Long
    Dim nextEntry As String
    
    ufEventsDisabled = True
    With aListbox
        .Tag = .Text
        .Clear
        For i = 0 To 25
            nextEntry = Chr(65 + i)
            If (nextEntry <> ListBox1.Text) And (nextEntry <> ListBox2.Text) And (nextEntry <> ListBox3.Text) Then
                .AddItem nextEntry
                If nextEntry = .Tag Then .Tag = .ListCount - 1
            End If
        Next i
        
        If IsNumeric(.Tag) Then .ListIndex = Val(.Tag)
    End With
    ufEventsDisabled = False
End Sub

Private Sub UserForm_Initialize()
    Call FillWhileChecking(ListBox1)
    Call FillWhileChecking(ListBox2)
    Call FillWhileChecking(ListBox3)
End Sub

 

[CaliKidd]

Mick and Mike,
Thanks so much to the both of you. I really appreciate your help! I am always amazed at the depth of knowledge and generosity of people on this forum. It inspires me and also makes me realize how much more there is to learn. Even the smallest techniques are helpful, such as using a For Next loop and Chr (65 - 90), to load the alphabet into the listbox. It sure is a much better/smarter way than using .additem "A", .additem "B"... etc!

Mick, the code you provided didn't work exactly like I intended, the difference being the selection on one listbox modified the selection all of the others without retaining the selection. For example, if I choose "A" on one listbox (Let's say, listbox4), all other list boxes don't have "A" on their selection lists, but when I select another letter, "K", on a different listbox, then all of the other selection lists on the listboxes remove "K", but now include "A", including listbox4. Perhaps my original "spec" wasn't clear and I'm sure it could be tweaked, but I am still very appreciative of your help. In an effort to learn a little more, I did have a question for you, if you don't mind:

In the basic module, you have a line:

If Not Chr(n) = tom Then

What exactly does the "tom" string do?
And is If Not Chr = "tom" the same as If Chr <> "tom"? If so, is one better/faster than the other? If they are the same, I would tend to write it the latter way, but only because my brain tends to think that way.

Mike,
Your example worked perfectly. The use of the .tag property is interesting. I don't mean to show my ignorance, but I never even realized there was a .tag property! I've never used it. I'm not sure I fully understand it yet, but it seems like an extra value placeholder. Is that correct? I'm usually trying to read the object's .value or .text properties directly, or defining a whole new variable that I can store the object's .value or .text in, so it's pretty cool to have a .tag field laying around within the control itself that you can utilize.