Solved: Populate Combobox with spreadsheet col

[jwise]

I couldn't find this via search. I have a combobox on a userform. There is a data worksheet that has A1:A42 with property names. I want the user to select one of these properties. I know how to populate the combobox with code, but I want to get the option list from this data worksheet because this is easier to maintain.

Said another way, if worksheet "DATA" cell A1 has "ABC", A2 has "DEF" and A3 has "GHI", how do I get a drop down list so I can only choose these three values on a userform?

TIA

[mikerickson]

UserForm1.ComboBox1.List = Range("A1:A3").Value

[Kenneth Hobs]

One can also use RowSource:
Private Sub UserForm_Initialize()
'ComboBox1.List = Range("A1:A3").Value
'ComboBox1.RowSource = "A1:A3"
'MyRange refers to: =OFFSET(Sheet2!$A$2,0,0,MATCH("*",Sheet2!$A:$A,-1),1)
ComboBox1.RowSource = "MyRange"
End Sub
You can also use named ranges as I did above using a dynamic named range.

[jwise]

Thanks for the suggestion. It's amazing how much of my programming is now controlled by my ability or inability to find what I need.

Thanks again.

[jwise]

Thanks. You posted while I was replying to Mike, so I didn't see your reply until later. I have adapted your code to fit my particularities.

[CreganTur]

If you use a named range (which is what I do), you can sometimes run into the problem of needing to add data to your named range. If the data in this range is going to change often, you can use the following code that I got from Jake (DRJ) to make your named range dynamic:

Dim x As Long
x = Sheets("Sheet2").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="RangeName", RefersTo:=Sheets("Sheet2").Range("A1:A" & x)


Just change the range's name to match the one you're working with, and the actual cell ranges to meet your requirements.

[mdmackillop]

To create a dynamic range name on the worksheet, use the Offset function

[jwise]

Randy & mdmackillop,

Thanks for the insight. I am checking into this. Named Ranges is something I've never used, so I'm trying to assimilate the concepts.

I really appreciate the code snippet and the screen image... both worth a thousand words.