Combo Boxes

[Scot Rundell]

I am tring to use a combo box to select 1 of 5 specific
list fill ranges that I have defined in another
spreadsheet. Can someone help me out with how I do this.

 

[Tom Ogilvy]

by defined, do you mean insert=>Name=>Define

Just load the 5 names in the combobox as strings

Then when the selection is made, use the value of the combobox to assign the
listfillrange of another combobox (assume that is what you want to do)

Me.Combobox2.ListFillRange =
worksheets("Data").Range(Me.combobox1.Value).Address(external:=True)

Note that there are 3 different types of comboboxes (2 of which have a
listfillrange property). It is best to state what controls you are working
with.

 

[Scot Rundell]

Thanks for the help, but this is what I ended up using....
Select Case ComboBox4.Value
Case "VIC/TAS"
ComboBox5.ListFillRange = "Branch_Name_VIC"
ComboBox2.ListFillRange = "Estimator_VIC"
Case "QLD"
ComboBox5.ListFillRange = "Branch_Name_QLD"
ComboBox2.ListFillRange = "Estimator_QLD"
End Select

Regards,
Scot Rundell



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Tom Ogilvy]

Combobox5.ListFillRange = "Branch_Name_" & left(Combobox4.Value,3)
ComboBox2.ListFillRange = "Estimator_" & Left(combobox4.Value,3)

would be simpler if you set up your names to correspond.

If you didn't, then you can go ahead and use redundant code.

 

[Scot Rundell]

-----Original Message-----
Combobox5.ListFillRange = "Branch_Name_" & left (Combobox4.Value,3)
ComboBox2.ListFillRange = "Estimator_" & Left (combobox4.Value,3)

would be simpler if you set up your names to correspond.

If you didn't, then you can go ahead and use redundant code.

--
Regards,
Tom Ogilvy






.
Is this code used inside the case statement or is it

seperate?

 

[Tom Ogilvy]

You wouldn't need the case statement - it builds the defined name base on
the vlaue of combobox5


Again, this only works if the first 3 letters of each selection in the
combobox5 would build the proper defined name.


Assume the choices are in combobox 5 are

AAAAAA
BBB
CCC/DDD
EEEEE
FFF-YYY-ZZZ

then using this method, you would build names like

Branch_Name_AAA
Branch_Name_BBB
Branch_Name_CCC
Branch_Name_EEE
Branch_Name_FFF

This fit the pattern for the four names (two Combobox5 values) that you
showed. I can't guess what the other ones are, but just suggested an
approach which doesn't require a case statement with five different
conditions (if you use consistent defined names).