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:
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?
Code:
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 WithOK, 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?
Last edited:
