Indirect Function and Controls

[smonczka]

I'm trying to use the "Indirect" function with a combo box, but when I attempt to put in my formula in the properties of the combo box control I get "Reference is not valid." Is it posible to use functions inside of a Control? If not how else can I do this?

What I have is two combo controls, the first selects from an array of names, say a, b, c, d..., the second combo box is dependant on the what is selected in the first. So if you select A from the first box the second will show only options for A, such as A1, A2, A3. If you selected B from the first combo box you would get a diferent set of option in the second box, such as B1, B2, B3.

I'm using excel 2007 to do this and I have done it using "Data Validation" and the Indirect function... for example "=INDIRECT(VLOOKUP(B2,Array,2,FALSE))". But I need to use an actual combo box this time.

Thanks for the help,

 

[James_B]

I guess there are probably a thousand different ways of doing such a thing. One way to go would be use appropriately named ranges. That is, define a range called, say, "rCars" (containing different types of cars). And then when "Cars" is selected from ComboBox1, populate ComboBox2 accordingly. Something like:

ComboBox2.List = Application.Transpose _
   (Range("r" & CStr(ComboBox1.Value)).Value)

assuming "rCars" is a column. A bit fiddly perhaps but it should work.

 

[James_B]

Alternatively, if you had your options in a table--that is, if you had headings like Fruits, Meats, etc, underneath which were listed options like Apples, Oranges, etc, and Pork, Beef, etc--then you could use something like

nHead = Application.Match(ComboBox1.Value, _
   Range("A1").Resize(, Range("A1").End(xlRight)), False)
ComboBox2.List = Application.Transpose(Range("A1")(, nHead) _
   .Resize(Range("A1")(, nHead).End(xlDown)).Value)

assuming I have these End statements right (I can't quite remember how they work) and that your table begins in cell A1. This might be a bit less fiddly to set up.

 

[James_B]

Sorry, I probably meant something more like

nHead = Application.Match(ComboBox1.Value, _
   Range("A1").Resize(, Range("A1").End(xlRight).Row), False)
ComboBox2.List = Application.Transpose(Range("A1")(, nHead) _
   .Resize(Range("A1")(, nHead).End(xlDown).Column).Value)

there...

 

[smonczka]

James, thank you very much for your reply, with a little modification that did the trick.

 

[Dynamo Nath]

Hi James,

I'm trying to do a similar thing in Excel and have tried the initial method you posted but keep getting an error message
"Method 'Range' of object '_Global' failed

I understand what the line of code is trying to do and I'm obviously missing something fairly basic. Here is what I've renamed your code as:

frm_cbEngName.List = Application.Transpose(Range("r" & CStr _(frm_cbSoftware.Value)).Value)

Would it make a difference if I used list boxes as opposed to combo boxes? Also, am I correct in assuming that I can use this method to then reference a 3rd box based on the 2nd?

many thanks,

Nathan

 

[smonczka]

Nathen, here is an example of three different ways that I found you can do this...

http://www.xl-expert.com/IndirectValidation.htm

Good Luck
Steve

 

[Dynamo Nath]

Hi Steve,

Thanks for this. With a bit of tweaking I managed to get one of them to work. As I'm still learning it was good it didn't work first time out and I had to think it through to get the desired results.