combo boxes

[mwam423]

greetings! i have two combo boxes in a user form and would like to run a
macro once a choice is made in one of the combo boxes. macro narrows down
the possibilities in subsequent combo box once a selection is first made in
either combo box.

example: combo box 1 is a choice of companies: A, B, C, or D. combo box 2
is list of products sold to that company. would like the list of products
to be able to change depending on which company is selected. additionally,
if a product is chosen from combo box 2, would like combo box 1 to show only
those companies that buy that particular product.

or does it make more sense to have two user forms??

 

[Joel]

It shouldn't make a difference if you use 1 or 2 comboboxes. You can also
use a list boxex.

The userform should have a Contol Button to exit the userform. The click
function for each user box should update the other list boxes as necessary.

I think you need 4 list boxes not 2.
1) Lists all Companies
2) List Products that selected Companies sell
3) List All Products
4) List Comanies that sell selected products.

Another suggestion.
Use 2 Buttons to either select Companies or Products. Then have two list
boxes with the order based on the selected button.

 

[JLGWhiz]

You could probably do it with two comboboxes. You would need either an If
Then ElseIf Then type algorithm or a Case statement in your first combobox
code so the second combobox would load the right set of product. It would
probably be easier to set up the product lists in four separate ranges than
to use the add item method to load the second combobox. ComboBox1 code would
be something like this:

Sub ComboBox1_Click()
Set ProdRng1 = Range("AA1:AA100")
Set ProdRng2 = Range("BB1:BB100")
If ComboBox1.Value = "Company A" Then
ComboBox2.RowSource = ProdRng1
ElseIf ComboBox1.Value = '"Company B" Then
ComboBix2.RowSource = ProdRng2
'...etc

This is only for illustration purposes, to give you some ideas of approach.

 

[mwam423]

this probably won't work as i've 48 unique companies and counting, and the
product list for many companies will change over time.

 

[Joel]

Your idea should worked without any problems. If you want me to help you can
send me your spreadsheet and I will get code for the boxes started.

 

[mwam423]

hi joel, sorry haven't gotten back to you as i've been working on some other
projects. is your offer still good, about helping with code?

i've got a macro that pretty much works (using two userforms) but would like
to see it with single userform. also would like you to take a look at
what/how i'm approaching this task; this is the first time i've used combo
boxes so i'd really appreciate your feedback, thanks.

 

[Joel]

Do you want to select and or enter info into combobox? Combobox allows
manual entry, list box allows only select items. Not sure if it is bettter
to use l2 list boxes or comboboxes. I think it is better to put both boxes
on the same user form. Send me the file and I will take a look.

(e-mail address removed)

 

[mwam423]

hi joel, using got combo boxes now but user is limited to menu items so
perhaps list boxes would be appropriate . .