Combo Box

[ajx1220]

I've been fighting with this all day. What I am trying to do is to get a drop down that when one of the options is selected it populated 2 other values into 2 other cells. Lets say I have a drop down of cars. GMC Envoy, Toyota Highlander, Hummer H2. I want the user of the spreadsheet to be able to select GMC Envoy and have the MSRP populate in one cell and the Invoice price populate in another cell. I cannot figure this out for the life of me and any help would be appreciated. Thanks

[Leith Ross]

Hello ajx1220,

This can be accomplished easily using a Control Toolbox combobox, since it supports multiple columns. I assume this combobox is on a worksheet and not a VBA UserForm. If not, let me know. Do you have the cars and prices listed on a worksheet in individual lists? If so, what are the cell ranges you are using?

Sincerely,
Leith Ross

[ajx1220]

I just used the cars as an example. I have a sheet in the workbook that has what would be the models, the MSRP, and the Invoice all in columns. A is Models, B is MSRP, C is Invoice. I need A to be the only thing in the combo box and when the model is selected I need B and C to populate in two seperate cells.

[Leith Ross]

Hello ajx1220,

Not a problem. The ComboBox can display only column and hide the others. When the user makes their selection, the Click event of the combo can then load the other cells with the hidden data from the ComboBox. Give me a few minutes and I can code up and example for you.

Sincerely,
Leith Ross

[ajx1220]

Thanks, I think I found a solution. =INDEX(A2:A4, C5) using this I can take the numeric output from the Combo Box and have it referenced to populate the 2 fields I need filled. It's messy but will do. Unless you have a cleaner solution that doesnt require me hiding things in plain sight.

[ajx1220]

Also, I need help figuring out how to have multiple options selected and have a dollar value for the multiple options calculated in a different cell. I was thinking check boxes or list box. For example, I'll need a box for apples , bananas, pears, and grapes. In a seperate sheet (Sheet 3) I have a table. Column A is apples, bananas, pears, and grapes, and Column B is the price for these items. I need the items selected on Sheet 1 and the dollar amount for each item selected calculated into a cell on Sheet 1 pulling the prices from Sheet 3.

[Leith Ross]

Hello ajx12220,

Have a look a this example workbook, and let me know if this close to what you want.

Sincerely,
Leith Ross

[ajx1220]

Thanks, how do I apply this to what I need it for. The format is there but how do I plug this into my current workbook or how do I plug my data into this workbook?

[Leith Ross]

Hello ajx1220,

"Sheet2" is the list source. All the data on this sheet is loaded into the ComboBox using the macro "LoadComboBox". This macro automatically when the Workbook opens. You can call it from code anytime the lists are updated or run it manually by using ALT+F8 to call the macros dialog.

If you need help integrating this into your workbook, zip it and then attach it with your next post. I can make the needed additions for you.

Sincerely,
Leith Ross

[ajx1220]

Thanks, I got it. This Macros stuff is all new to me. I knew it could be done just couldnt figure out how. Any idea on my other issue posted above?

[Leith Ross]

Hello ajx12220,

Have a look at this link.

Creating Dependent Lists

Sincerely,
Leith Ross

[ajx1220]

I'm not too sure that helps. I'm looking to have several check boxes or list box so multiple items can be selected. Similar to my first issue, I need two different cells populated with prices but this time around I need the selected options calculated and then populated into the cells.

[Leith Ross]

Hello ajx1220,

Your description is a bit sketchy. Can you give me an example of how you want this to work?

Sincerely,
Leith Ross

[ajx1220]

I'm using this workbook to assist in calculating profit made on merchandise. In the calculation I need to be able to take into account some variable options. I want to be able to select these options (multiple may be selected) and have the monetary value of them calculated into two cells.

Take the car example from my first post. Instead this time I need to be able to select, 1, 2, or all 3 of the vehicles at once and have their MSRP and Invoice calculated into one cell. So lets say I select Envoy and H2, I would need the MSRP of these 2 vehicles added together and populated into a cell, and I would need the same done with the Invoice. Clearer?

[Leith Ross]

Hello ajx1220,

Have a look at this workbook example.

Sincerely,
Leith Ross

[VBA Noob]

ajx1220,

Please read forum rules below. Code needs to be wrapped as per rule 5. Also your title as being amended as Titles like Urgent help are not permitted

VBA Noob