Dynamic combobox items in VBA

Question

I have a simple Excel user form with 3 comboboxes with a few items for the user to select, "Option A", "Option B", "Option C" in each box. I would like to store whichever option the user selects in any box and then remove that item from the other 2 boxes. If the user changes the item selected in any box, I need that item to be added back to the items available to choose from in the other 2 boxes. To get started, I tried to store each item selected for each box in a "used" variable and then add back to the list depending on user selection: I know how to add items by their name reference using name e.g "Option A", but to remove them, the only method I know is to refer to their index number, which is not helpful as this changes depending on the number of items in the combobox list. So if any option is selected from a box, that option needs to be removed from the other 2 boxes, and added back if the section changes. Once the user has selected items in the first 2 boxes, the last box should only have one item to select from. I have:

With ComboBox1
    .AddItem "Option A"
    .AddItem "Option B"
    .AddItem "Option C"  

End With

ComboBox1.RemoveItem 1    'need to refer to item by name instead: remove "Option A" ?

Please be specific as I'm not an advanced VBA user. Any help would be appreciated.

ironstone13 ironstone13 · Accepted Answer · 2017-01-08T10:19:24

You can assign names to controls dynamically and then use names rather than indexes to refer to those controls. Alternatively, you can declare a collection or variables in code and store references to those comboboxes in there (see control arrays)

I would, however strongly advice you against dynamically removing controls - disabling them gives a much better user experience.

If you would still like them to disappear, you can alter their visibility property, again avoiding deletion.

Furthermore, it looks like you're really trying to achieve selection of non duplicate elements in a specific order. Isn't there some kind of list control that would suit this purpose better?

Dynamic combobox items in VBA

1 Answers