Excel VBA userform Combobox eliminate selection

[yimingtan]

Hi all,

Im new to Excel VBA userform and I need you guy's help for following problem.

Im currently working to create an excel userform which contain 10 combobox, and I want the user to select item in it but the item will not repeat on the next combobox if been selected from previous combobox.

Example:
With a Userform that have 5 combobox for user to choose item from the list

Select the top 5 favorite food from the list:
-Apple
-Cake
-Fish
-Beer
-Chicken
-Banana
-Pineapple
-Orange

If the user had selected "Apple" on the 1st combobox, the choices in Combobox2 list will not have "Apple" in it.
if combobox1 selected " Apple", combobox2 selected "Orange", in the list of combobox3 will not have "Apple" and "Orange" etc......

Anyone can give me some advice? Your help is much appreciated!

 

[My Aswer Is This]

Are you now saying you want combox 2 to 10 hidden till a choice from combobox1 is made?

And then unhide combobox 2 after a selection from combobox is made.

What would be the purpose in hiding them?

You said:

I also have an idea where limit the user to use the combobox by sequence(example, the user unable to saw the drop down item in combobox2 if no item been select from combobox1 etc) is this easier to create the script which going to eliminate the selected item from the list?


If I understand correctly the entire project is so a user can choose what item he likes best from a list of 20 or more items.

He must choose them in order of likeness.
He can only choose 10

I still think it would be easier for a user to just select the items from one listbox.

As he makes his choice his choice is added to listbox2
The choice he makes in listbox1 is now removed from listbox1 so he cannot choose that item twice.

With my ideal the user can see this list in listbox2 which clearly shows all ten of his choices.

If the user wants to start over we could have a button he could click and the two listboxes would be reset back to what it was at the beginning.

Your plan would require the user to look at all 10 comboboxes to see the choices he has made.

​

 

[yimingtan]

Hi My Answer Is This,

The purpose of hiding the next combobox (or clear the choices of the all remaining combobox) is to ensure the user have to choose the item base on likeness without skip any combobox (user only able to choose the item start from combobox1, then combobox2 then combobox3 etc).

see below scenario :
If 1st attempt of user has following answer with wrong item selected:
Combobox1: Apple
Combobox2: Orange
Combobox3: Pineapple<<<this should be number 4 instead of 3
Combobox4: Beer<<<<this should be number 3 instead of 4
Combobox5: Cake

when user reselect correct answer on Combobox3, the item on the Combobox4 and Combobox5 will be cleared so that user need to re-fill only combobox4 and combobox5 instead of whole combobox list.

The effect might be not obvious in this scenario, but in real case with 10 combobox if the user mistakenly fill in combobox7 or combobox8 this "clear remaining combobox feature" able to reduce the time to refill the form which can save some times. (they only need to correct from combobox7 instead of starting over).


For the 2listbox method, I have few question hope that you could help to clarify

1. if the user accidentally wrongly select the itemlist on the halfway of filling the userform (example item number 6 wrongly select a food) the user need to reselect all listbox item? or it have feature such as "undo" that particular selection?

2. If using 2listbox method, can I add additional script base on the selected item? as for now Im able to add additional using "multiple combobox" method

Example:
If the selected list item contain unhealthy food (beer, pizza, chips etc), after user complete the userform and click print(where the userform info will be transfer into an excel template). The side or another location will print "Unhealthy Food" or some extra message

In my current "multi-combobox" method I use code like below for each combobox:

Dim choosefood As Long
choosefood= ComboBox2.ListIndex
Select Case ComboBox2.Value


Case Is = "Beer"
Cells(6, 3).Value = "Beer"
Cells(6, 4).Value = "Unhealthy Food"


Case Is = "Cake"
Cells(6, 3).Value = "Cake"
Cells(6, 4).Value = "Unhealthy Food"

Case Is = "Orange"
Cells(6, 3).Value = "Orange"
Cells(6, 4).Value = "Healthy Food"

Case Is = "Apple"
Cells(6, 3).Value = "Apple"
Cells(6, 4).Value = "Healthy Food"

Case Is = "Pineapple"
Cells(6, 3).Value = "Pineapple"
Cells(6, 4).Value = "Healthy Food"

'etc of remaining 20+ item


End Select

Not sure if listbox method able to do so?

 

[My Aswer Is This]

So try this and see if this would work.

You need one command Button Name CommandButton2
One listbox named ListBox1
One Listbox Named ListBox2

Now when you double click on any item in ListBox 1 that value will now be entered into Listbox2
You must enter 8 values in Listbox2 or when you try to run the script you will get a message box saying you have not selected 8 You can change this. After you get all 8 items in listbox2 press button CommandButton2 and your script will put your values into sheet(1) where you said start in row(6) Column(3)

Now later you can modify script to your needs.

Now if user makes mistake he can double click on value in listbox2 and it will be deleted from listbox2 and added back into Listbox1.

I have not added the part about healthy foods. I will do that later if what I have here will work for you.

Private Sub CommandButton2_Click()
Dim i As Long
   If ListBox2.ListCount <> 8 Then: MsgBox "You failed to select all items": Exit Sub
    For i = 0 To ListBox2.ListCount - 1
        Sheets(1).Cells(i + 6, 3).Value = ListBox2.List(i)
        ListBox2.Selected(i) = False
Next
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox1.ListCount = 0 Then Exit Sub
If ListBox1.Text = "" Then Exit Sub
ListBox2.AddItem ListBox1.Text
ListBox1.RemoveItem ListBox1.ListIndex
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox2.Text = "" Then Exit Sub
ListBox1.AddItem ListBox2.Text
ListBox2.RemoveItem ListBox2.ListIndex
End Sub
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Apple"
.AddItem "Cake"
.AddItem "Fish"
.AddItem "Beer"
.AddItem "Chicken"
.AddItem "Banana"
.AddItem "Pineapple"
.AddItem "Orange"
End With
End Sub

 

[My Aswer Is This]

I now have a script that also includes the Healthy and Un Healthy.

You still need Listbox1 and Listbox2 and CommandButton3
You will see my command button is now 3 not 2

Private Sub CommandButton3_Click()
'New Modified 2-24-18 10:50 PM EST
Dim i As Long
   If ListBox2.ListCount <> 5 Then: MsgBox "You failed to select all items": Exit Sub
    For i = 0 To ListBox2.ListCount - 1
        
    MyVal = ListBox2.List(i)
With Cells(i + 6, 3)
.Value = ListBox2.List(i)
            Select Case MyVal
                      Case "Beer", "Cake"
                          .Offset(0, 1).Value = "Un Healthy Food"
                       Case "Orange", "Apple", "Pineapple"
                         .Offset(0, 1).Value = "Health Food"
                        
            End Select
        End With
    Next
End Sub
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox1.ListCount = 0 Then Exit Sub
If ListBox1.Text = "" Then Exit Sub
ListBox2.AddItem ListBox1.Text
ListBox1.RemoveItem ListBox1.ListIndex
End Sub
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If ListBox2.Text = "" Then Exit Sub
ListBox1.AddItem ListBox2.Text
ListBox2.RemoveItem ListBox2.ListIndex
End Sub
Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Beer"
.AddItem "Cake"
.AddItem "Orange"
.AddItem "Apple"
.AddItem "Pineapple"
End With
End Sub

 

[My Aswer Is This]

In case your interested here is a way to load your Listbox with values.
My example loads five values.

May be a little easier code to write.

Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To 5
ListBox1.AddItem (Choose(i, "Beer", "Cake", "Orange", "Apple", "Pineapple"))
Next
End Sub

 

[yimingtan]

Hi My Answer Is This,


Thanks for the script!, for now i tried the script it looks fine but need to have some changes on the code to fit it into my project, will come back here if face any problem.

Just want to know, if I want to "clear" the listbox2(where all selected item return to listbox1), any way to do so?

I tried "Listbox2.clear" where all item been deleted(not return to listbox1)
and "Listbox2.ListIndex = -1" but nothing happen

 

[My Aswer Is This]

To clear the listbox2 you would use:
ListBox2.Clear

So I'm not sure why this does not work for you.

Now if your wanting to copy all the values from ListBox2 into Listbox1 and then clear all the values from ListBox2

You can try this:

Private Sub CommandButton4_Click()
ListBox1.List = ListBox2.List
ListBox2.Clear
End Sub