CODEWITHSUNDEEP
excelvba
Vaupell
Vaupell

Reputation: 105

Use a listcolumn to fill a combobox in userform

I am using combo boxes quite a lot, and often i refer the rowsource in the properties like this sheetx!tablename

However, when i would like to use column 2 or as in the picture below called "Name" i feel its annoying that i have to write a loop in the userform initialization to fill the combobox.

I was wondering and have been trying without success using listobject databydyrange listcolumn to fill but no luck.

Now i wonder, Can i use a listobject column reference in the combobox rowsource?

Here is the code that gives me the error:

Worksheets("Engine").ListObjects("PeopleList").ListColumns("Name").Range.Select

enter image description here

Upvotes: 1

Views: 1516

Answers (1)

jamheadart
jamheadart

Reputation: 5313

You can set a combobox ListFillRange:

Sheet1.ComboBox1.ListFillRange = "Sheet1!A2:A7"

A ListColumn has a Range so you can use its Address property:

Sheet1.ComboBox1.ListFillRange = Worksheets("Engine").ListObjects("PeopleList").ListColumns("Name").Range.Address

This will bring in the column header too so like you mentioned, you can use the DataBodyRange and us its .Address property instead.

Sheet1.ComboBox1.ListFillRange = Worksheets("Engine").ListObjects("PeopleList").ListColumns("Name").DataBodyRange.Address

EDIT

As per comments, .ListFillRange exists on ActiveX form controls only, so for a UserForm ComboBox use Rowsource

Sheet1.ComboBox1.RowSource = myRange

Upvotes: 1

Related Questions