combo box help

[littlegreenmen1]

i am trying to find a way to resize a combo box depending on how man
possible choices there are. for example, i have a combo box (cb#1
that allows me to select from a list of vendors. the drop down size o
this box stays constant. then in the second combo box (cb#2) a list o
materials is displayed. my problem is that not all vendors have th
same number of materials. so if i select vendor A (who has
materials) in cb#1, then i would like cb#2 to have a drop down lin
amount of 3. then if i pick vendor B (who has 100-item long list)
would need the drop down line amount to be 100. right now it's set fo
the maximum number of drop down lines which is a bit overkill if there'
only 3 items. i dont know if it matters but i'm using the combo bo
from the form toolbar not the control toolbar. any help on my dilem
would be much appreciated. thank you in advance

 

[Dave Peterson]

I put two comboboxes from the Control toolbox toolbar on a new worksheet.

I double clicked on the top one and inserted this code:

Option Explicit
Private Sub ComboBox1_Change()
Select Case LCase(Me.ComboBox1.Value)
Case Is = "aaa", "bbb", "ccc"
Me.ComboBox2.ListRows = 8
Case Else
Me.ComboBox2.ListRows = 25
End Select
End Sub

I put test data in A1:A100 and assigned .listfillrange of each to A1:A100

 

[littlegreenmen1]

I appreciate the reply, but that wasnt exactly what I was looking for.
That uses 2 combo boxes based off of one set of data (a1:a100). wha
i'm trying to do is take combo box #1 to pick between say (a1:a2).
then, if combo box #1=a1 then combo box #2 would need to display
items. but, if combo box #1=a2, then combo box #2 would need to selec
from 50 items. for example, let's say combo box #1 has either th
option of color or size. if you choose color then combo box #2 has
choice of red, blue, yellow, white, black, green, grey, purple, brown.
and if you choose size from combo box #1 then you have a choice of big
medium, or small in combo box #2. i've figured this part out. wha
i'm confused on is, if you choose color from combo box #1 then combo
box #2 needs ListRows=9, but if you choose size from combo box #1 the
combo box #2 needs ListRows=3. so i need ListRows to change dependin
on how many options are given by your choice from combo box #1. I hop
this helps to clarify my dilema

 

[Dave Peterson]

If you know how many options are in the dropdown, can't you just make the
listrows that same number?

 

[littlegreenmen1]

ok, i think i realize where i'm not making myself clear. the number o
options for combo box #2 varies depending on what you choose from comb
box #1. so, in order to include all the possiblities (from our colo
example earlier) i would have to set the row amount to 9 in combo bo
#2 to include all the colors. but, if i picked size, the row amoun
for combo box #2 would still be 9 even though i only need it to be 3.
how i have it setup: cell a1=color a2=size. then, in b1:b9 (if comb
box #1=color) is the list of colors; if combo box #1=a2, then b1:b3 ar
the sizes and b4:b9 are "0". b1:b9 change depending on what you selec
in combo box #1. the range for combo box #2 has to be b1:b9 to includ
all the colors. and in order to display all the colors i have a ro
amount of 9 (i could set it to 3 and that would help my problem bu
then i would have to scroll alot because in my actual form the numbe
of choices varies from 3 to 100+). having the row list amount at 9 i
fine for color, but if it's size that's 6 rows longer than i need whic
are simply filled with blank space. again, it's not that big of a dea
for a difference of 3 to 9, but from 3 to 100+ that's a lot of blan
spaces. so what i'm trying to do is set the listrow amount equal t
the number of cells in b1:b9 that have a value other than 0, but i don
think i can include a formula under listrow amount, i think it has to b
simply a range of cells. i hope this helps to clarify. if you need an
more information or if you need me to try and say it a different wa
please let me know. i really do appreciate the help

 

[Dave Peterson]

When you change the value in combobox1, how do you populate the list for
combobox2?

And are these comboboxes on a userform or are they on a worksheet?

If they are on a worksheet, are they from the Forms toolbar or from Control
toolbox toolbar?