Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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. "mwam423" wrote: 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?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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" wrote: 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?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
this probably won't work as i've 48 unique companies and counting, and the
product list for many companies will change over time. "JLGWhiz" wrote: 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" wrote: 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?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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" wrote: 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?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
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. "mwam423" wrote: 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" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
combo boxes
hi joel, using got combo boxes now but user is limited to menu items so
perhaps list boxes would be appropriate . . "Joel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
Filtered list for Combo Box ListFillRange - Nested Combo Boxes | Excel Programming | |||
Combo Boxes and Tick Boxes | Excel Programming |