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


--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

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 wrote:

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

--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

--

Dave Peterson

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). what
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 3
items. but, if combo box #1=a2, then combo box #2 would need to select
from 50 items. for example, let's say combo box #1 has either the
option of color or size. if you choose color then combo box #2 has a
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. what
i'm confused on is, if you choose color from combo box #1 then combox
box #2 needs ListRows=9, but if you choose size from combo box #1 then
combo box #2 needs ListRows=3. so i need ListRows to change depending
on how many options are given by your choice from combo box #1. I hope
this helps to clarify my dilema.


--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

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

littlegreenmen1 wrote:

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). what
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 3
items. but, if combo box #1=a2, then combo box #2 would need to select
from 50 items. for example, let's say combo box #1 has either the
option of color or size. if you choose color then combo box #2 has a
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. what
i'm confused on is, if you choose color from combo box #1 then combox
box #2 needs ListRows=9, but if you choose size from combo box #1 then
combo box #2 needs ListRows=3. so i need ListRows to change depending
on how many options are given by your choice from combo box #1. I hope
this helps to clarify my dilema.

--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

--

Dave Peterson

ok, i think i realize where i'm not making myself clear. the number of
options for combo box #2 varies depending on what you choose from combo
box #1. so, in order to include all the possiblities (from our color
example earlier) i would have to set the row amount to 9 in combo box
#2 to include all the colors. but, if i picked size, the row amount
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 combo
box #1=color) is the list of colors; if combo box #1=a2, then b1:b3 are
the sizes and b4:b9 are "0". b1:b9 change depending on what you select
in combo box #1. the range for combo box #2 has to be b1:b9 to include
all the colors. and in order to display all the colors i have a row
amount of 9 (i could set it to 3 and that would help my problem but
then i would have to scroll alot because in my actual form the number
of choices varies from 3 to 100+). having the row list amount at 9 is
fine for color, but if it's size that's 6 rows longer than i need which
are simply filled with blank space. again, it's not that big of a deal
for a difference of 3 to 9, but from 3 to 100+ that's a lot of blank
spaces. so what i'm trying to do is set the listrow amount equal to
the number of cells in b1:b9 that have a value other than 0, but i dont
think i can include a formula under listrow amount, i think it has to be
simply a range of cells. i hope this helps to clarify. if you need any
more information or if you need me to try and say it a different way
please let me know. i really do appreciate the help.


--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

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?



littlegreenmen1 wrote:

ok, i think i realize where i'm not making myself clear. the number of
options for combo box #2 varies depending on what you choose from combo
box #1. so, in order to include all the possiblities (from our color
example earlier) i would have to set the row amount to 9 in combo box
#2 to include all the colors. but, if i picked size, the row amount
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 combo
box #1=color) is the list of colors; if combo box #1=a2, then b1:b3 are
the sizes and b4:b9 are "0". b1:b9 change depending on what you select
in combo box #1. the range for combo box #2 has to be b1:b9 to include
all the colors. and in order to display all the colors i have a row
amount of 9 (i could set it to 3 and that would help my problem but
then i would have to scroll alot because in my actual form the number
of choices varies from 3 to 100+). having the row list amount at 9 is
fine for color, but if it's size that's 6 rows longer than i need which
are simply filled with blank space. again, it's not that big of a deal
for a difference of 3 to 9, but from 3 to 100+ that's a lot of blank
spaces. so what i'm trying to do is set the listrow amount equal to
the number of cells in b1:b9 that have a value other than 0, but i dont
think i can include a formula under listrow amount, i think it has to be
simply a range of cells. i hope this helps to clarify. if you need any
more information or if you need me to try and say it a different way
please let me know. i really do appreciate the help.

--
littlegreenmen1
------------------------------------------------------------------------
littlegreenmen1's Profile: http://www.excelforum.com/member.php...o&userid=23978
View this thread: http://www.excelforum.com/showthread...hreadid=376843

--

Dave Peterson