How to create a multiple drop-down lists in Excel using Developer?

[PetarTen]

Hi Folks, I'm struggling to find a short Video where to see how I can make a Multiple Drop-down Lists using Developer Mode Box.
I don't want to use the data validation tool for several reasons, so if anyone here has come across a video or online lecture or presentation where this "trick" is shown - please comment


Btw, this article here is useless - Insert a multiple-selection list box - Microsoft Support
The idea is to make a Multiple Dropdown Lists like this guy here:

...but using the Developer.
Cheers!

 

[PetarTen]

It doesn't appear
It's not working.

 

[6StringJazzer]

In my post I said

However, that item won't be automatically selected. It just appears in the list, and the user has to select it. If you want it to show up as automatically selected, you need to use VBA.

Do you want a VBA solution?

 

[PetarTen]

In my post I said

View attachment 118816

Do you want a VBA solution?

Yea please
I can save the file in XLSM format
Cheers!

 

[6StringJazzer]

Link to new file

Dropbox

www.dropbox.com

 

[PetarTen]

Link to new file

Dropbox

www.dropbox.com

Amazing!
If only #N/A can be removed from the right B2 cell - it would be really Awesome!
Btw, does this mean I can add TWO 4-cylnder Engines and have them listed in B2 Dropdown cell when BP is selected on the left (A1 cell) ?
For example if on Sheet2 I add e new 4-cylinder (5 stroke) engine, will it be visible in the B2 Dropdown cell ? (assuming the first Engine has 4 strokes)
PS Will be on the computer this Monday, so I cannot check if it's going to work IF I add an Engine - I cannot do it from my cellphone

 

[PetarTen]

Actually, I know how to hide the #N/A, by using IFERROR, so please disregard the first part of the previous post

 

[6StringJazzer]

The N/A shows if nothing is selected for Oil. Do you want it to be blank?

Btw, does this mean I can add TWO 4-cylnder Engines and have them listed in B2 Dropdown cell when BP is selected on the left (A1 cell) ?

It's better to give all your requirements up front. Yes, you can do this but you have to modify the named formula that populates the combobox. You were quite insistent that you wanted a single item in that dropdown.

“Why would you want a single value in a dropdown list?”, because that’s what I want Because this is the way I want to present the information.

 

[PetarTen]

The N/A shows if nothing is selected for Oil. Do you want it to be blank?


It's better to give all your requirements up front. Yes, you can do this but you have to modify the named formula that populates the combobox. You were quite insistent that you wanted a single item in that dropdown.

Nooo
I meant, that Combo Box (or I should say ListBox) looks bulgy and has the chance to step on more than one Cell, right?
When you told me about ListBox I immediately have found it....and tried it....but then - once I placed it on the screen, it looked bigger, bulgy and had the appearance of MANY rows.
On top of that, it has two buttons Up & Down...something which Isn't necessary at all.
I like ComboBox because it has only one Row
That's what I meant.

But that doesn't mean when the user choose BP, to find only one Engine (or Product) in the right Dropdown List.
Right?

"Yes, you can do this but you have to modify the named formula that populates the combobox."
In what sense?
What do you mean "the named Formula" ?
Are you saying I have to use a different Formula ?

Is it possible to tweak something on Sheet2 ?
For example to put A5:A6 in a Dynamic Table ? ...or something like that?

I like what you have achieved and the Dynamic appearance when choose BP, how the Table immediately shows those few Lines about "BP's or Shell properties..."
I like it and it should does exactly that!
But when the user click on the right Dropdown list, he/she also must discover other Engine types written bellow "4-Cyliner".
Does that make sense?

 

[6StringJazzer]

Sorry, there is no named formula. The formula that fills the ENGINE combobox is in cell F3. But it only returns a single value. If you want multiple values the whole thing needs to be redesigned.

If I am going to be able to help I need a very specific set of requirements from you. I have already spent a lot of time and we still do not have usable results.

When you select BP in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?
When you select Shell in the OIL combobox, what items do you want to be available in the ENGINE combobox? What do you want the default selection to be?

 

[PetarTen]

Hi Jeff,
Attached you can find the File:
Filebin | 5dshj2sm71x22qtm

I played a little bit with the Table - you've done an excellent Work of Art btw
But now:
- BP is working like a charm
- but Shell isn't working

I guess you're right, maybe the architecture need a bit of a tweak.
What you see when you select BP now, is what I want - a Default 4 Cylinder in B2 Cell and from the Dropdown list (scroll / arrow), to be able to select few more Engines - 4 Cylinder (5 stroke), 5 Cylinder, etc.
And the same should be applicable when you select Shell.
Bear in mind that in A2 Cell, BP and Shell, are simply - tip of the iceberg.
In A2 should be able to add Modil1, Valvoline, Petronas, etc.

In other words - both A2 and B2 Cells should have ONE ROW (obviously when using a Combobox) and plenty of Products within them.
Thank you so much for your Help, please If you accept Donations - tell me how to send you some bits of coins