SOLVED

RowSource Syntax in a Form ComboBox

Copper Contributor

I am using the following successfully to validate a form combo box [using the "RowSource" parameter] against a table on a work sheet with a single column :-

LookupData!Table_Query_from_harglive9

Does anybody know how to alter this to specify a specific column when the Table has more than one column. Either defining Column number or Name to be used. Tried numerous syntax but none work.

EG LookupData!Table_Query_from_harglive9[Column Name] etc.

[Apologies if duplicated but last post seems to have disappeared]

Many thanks.

 

4 Replies
best response confirmed by Andy Johnston (Copper Contributor)
Solution
Generally speaking using the RowSource to fill a combobox on a userform is frowned upon. I always write the data to the combobox using the List property directly, e.g.:
ComboBox1.List = Worksheets("YourSheet").Range("TheNamedRange").Value
Note that it is also a good idea to name the range you are intending to use as the rowsource.

Many thanks for the information Jan.

 

Your solution works well.

 

Just to point out I got it working like you implied, by naming the table column and then refering to this in the "RowSource" property. Can you explain why using the "Row Source" is frowned upon, as opposed to your method.

 

Thanks Again Andy.

Because it is
a. Unstable (i've seen problems with userforms using the rowsource)
b. Error prone (the rowsource property does not change when you insert or delete cells).
Inserting and deleting cells will only keep working if you use a range name as those adjust themselves.

thanks for the tip, but how can i make the listbox updated at the same time @Jan Karel Pieterse 

1 best response

Accepted Solutions
best response confirmed by Andy Johnston (Copper Contributor)
Solution
Generally speaking using the RowSource to fill a combobox on a userform is frowned upon. I always write the data to the combobox using the List property directly, e.g.:
ComboBox1.List = Worksheets("YourSheet").Range("TheNamedRange").Value
Note that it is also a good idea to name the range you are intending to use as the rowsource.

View solution in original post