SOLVED

RowSource Syntax in a Form ComboBox

%3CLINGO-SUB%20id%3D%22lingo-sub-106707%22%20slang%3D%22en-US%22%3ERowSource%20Syntax%20in%20a%20Form%20ComboBox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-106707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EI%20am%20using%20the%20following%20successfully%20to%20validate%20a%20form%20combo%20box%20%5Busing%20the%20%22RowSource%22%20parameter%5D%20against%20a%20table%20on%20a%20work%20sheet%20with%20a%20single%20column%20%3A-%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3ELookupData!Table_Query_from_harglive9%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EDoes%20anybody%20know%20how%20to%20alter%20this%20to%20specify%20a%20specific%20column%20when%20the%20Table%20has%20more%20than%20one%20column.%20Either%20defining%20Column%20number%20or%20Name%20to%20be%20used.%20Tried%20numerous%20syntax%20but%20none%20work.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EEG%20LookupData!Table_Query_from_harglive9%3CSTRONG%3E%5BColumn%20Name%5D%3C%2FSTRONG%3E%20etc.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%5BApologies%20if%20duplicated%20but%20last%20post%20seems%20to%20have%20disappeared%5D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EMany%20thanks.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-106707%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504276%22%20slang%3D%22en-US%22%3ERe%3A%20RowSource%20Syntax%20in%20a%20Form%20ComboBox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504276%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20for%20the%20tip%2C%20but%20how%20can%20i%20make%20the%20listbox%20updated%20at%20the%20same%20time%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-108303%22%20slang%3D%22en-US%22%3ERe%3A%20RowSource%20Syntax%20in%20a%20Form%20ComboBox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-108303%22%20slang%3D%22en-US%22%3EBecause%20it%20is%3CBR%20%2F%3Ea.%20Unstable%20(i've%20seen%20problems%20with%20userforms%20using%20the%20rowsource)%3CBR%20%2F%3Eb.%20Error%20prone%20(the%20rowsource%20property%20does%20not%20change%20when%20you%20insert%20or%20delete%20cells).%3CBR%20%2F%3EInserting%20and%20deleting%20cells%20will%20only%20keep%20working%20if%20you%20use%20a%20range%20name%20as%20those%20adjust%20themselves.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-107851%22%20slang%3D%22en-US%22%3ERe%3A%20RowSource%20Syntax%20in%20a%20Form%20ComboBox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-107851%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%20for%20the%20information%20Jan.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20solution%20works%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20point%20out%20I%20got%20it%20working%20like%20you%20implied%2C%20by%20naming%20the%20table%20column%20and%20then%20refering%20to%20this%20in%20the%20%22RowSource%22%20property.%20Can%20you%20explain%20why%20using%20the%20%22Row%20Source%22%20is%20frowned%20upon%2C%20as%20opposed%20to%20your%20method.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Again%20Andy.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-107413%22%20slang%3D%22en-US%22%3ERe%3A%20RowSource%20Syntax%20in%20a%20Form%20ComboBox%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-107413%22%20slang%3D%22en-US%22%3EGenerally%20speaking%20using%20the%20RowSource%20to%20fill%20a%20combobox%20on%20a%20userform%20is%20frowned%20upon.%20I%20always%20write%20the%20data%20to%20the%20combobox%20using%20the%20List%20property%20directly%2C%20e.g.%3A%3CBR%20%2F%3EComboBox1.List%20%3D%20Worksheets(%22YourSheet%22).Range(%22TheNamedRange%22).Value%3CBR%20%2F%3ENote%20that%20it%20is%20also%20a%20good%20idea%20to%20name%20the%20range%20you%20are%20intending%20to%20use%20as%20the%20rowsource.%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by Andy Johnston (New 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.
Highlighted

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.

Highlighted
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.
Highlighted

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