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]

Sounds like you wanting any selection made from any combobox to then be removed from all other comboboxes.

So if Apple is chosen from combobox1 delete Apple from all other comboboxes. And so on is this true?

How are you loading all these values into the comboxes.

Show us the script that loads the Combobox

What is your ultimate goal here?

What happens after you choose all the values you want from the comboboxes?

 

[yimingtan]

Hi My Answer Is This,

Yes, The targeted result is to let selected item will not be shown inside other combobox's item list.


Currently I only able to add item individually to each combobox which does not link to each combobox.


Example:
With ComboBox1
.AddItem "Apple"
.AddItem "Cake"
.AddItem "Fish"
.AddItem "Beer"
.AddItem "Chicken"
.AddItem "Banana"
.AddItem "Pineapple"
.AddItem "Orange"
End With

With ComboBox2
.AddItem "Apple"
.AddItem "Cake"
.AddItem "Fish"
.AddItem "Beer"
.AddItem "Chicken"
.AddItem "Banana"
.AddItem "Pineapple"
.AddItem "Orange"
End With

With ComboBox3
.AddItem "Apple"
.AddItem "Cake"
.AddItem "Fish"
.AddItem "Beer"
.AddItem "Chicken"
.AddItem "Banana"
.AddItem "Pineapple"
.AddItem "Orange"
End With
etc



The purpose of the user form is to record the user's favorite food base on the list. (by ranking system)

Example:
My Favorite food ranking
1st : Orange
2nd : Apple
3rd : Beer
4th : Cake
5th : Pineapple

 

[My Aswer Is This]

So what happens after the user selects all his favorite items?

Is this data now put into the sheet some place?

 

[yimingtan]

Hi My Aswer Is This,

Yes, after user complete fill up the form and click "submit" button in the user form, the data will be display in an excel sheet so that I can print it out.
Example:

Private Sub CommandButton1_Click()

Cells(6, 3).Value = ComboBox1.Value
Cells(7, 3).Value = ComboBox2.Value
Cells(8, 3).Value = ComboBox3.Value
Cells(9, 3).Value = ComboBox4.Value
Cells(10, 3).Value = ComboBox5.Value

End Sub

 

[My Aswer Is This]

Would you consider doing this another way?

Use two Listboxes

We load all the values like Apple into listbox1 when Userform opens

As you double click on a item in the listbox1 the item you double clicked on is added to listbox2 in the order you wanted and is deleted from listbox1.

Then when you click on a button all the values in listbox2 are added to your sheet the way you want.

This would only require 2 listboxes and one command button.

 

[yimingtan]

Hi My Aswer Is This,

If using 2 list box method, the order of the item can be edited during the process?? or the user need to reset the userform to change the selection?

Example:
the 1st time selected order:
Apple
Orange
Pineapple
Beer
Fish

Directly change the 1st attempts answer to the order below:
Beer
Apple
Pineapple
Fish
Orange


2 listboxes method might be workable for less item list, but in my real case, there are more then 20 item and the user need to select 10 out of it(with sequence order for each selected item) which I feel that easily cause confusion and error for the users.

 

[My Aswer Is This]

Using the two listbox ideal. The user would not be able to change his selections after he has made the selection.

Your saying if I understand correctly your using 10 comboboxes and user must:
And:

there are more then 20 item and the user need to select 10 out of it.

I assumed only one per combobox.

So when user selects one that value then must be deleted from all other comboboxes.

It's your project. I was just providing a ideal that would not require 10 Comboboxes.

My ideal would also show all the selections made in one Listbox which is easier I would think to see.
And when ready that list would be added to the sheet when a button is clicked.

But if you think that would be confusing then I will see how I can write a script to do what you want.

There would have to be some way to write into the script that when the button is pressed that it checks to be sure all comboboxes have been selected.

Have you written any of the script yet?

For example we would need to know what combobox value goes into what cell.
​

 

[yimingtan]

Hi My Answer Is This,

Exactly! the main problem i'm facing right now is that im unable to "link" between all the combobox so that the itemlist able to eliminate the chosen item.

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?

or

maybe in the sub of"Private Sub ComboBox"X"_Click()" of each combobox able to run a script which can check other combobox selected item and eliminate it on the current combobox?

 

[yimingtan]

Hi My Answer Is This,

This combobox section is one of section of my Project, I have the whole project script but which is not related to the combobox portion(for combobox value on which cell location I believe it can be edited after this right? as for now I didnt have fix cell location for combobox value to be placed in the excel sheet yet), thus its ok if you able to help by giving some suggestion