ListFillRange from another sheet

Question

I'm trying to fill a combobox using .ListFillRange from another sheet however the list is not being populated.

I have the combobox on "sheet1" and the data on "sheet2" in the cells A2:A3000

From sheet1 I have tried using:

set ws = ThisWorkbook.Worksheets("sheet2")
set Rng = ws.Range("A2:A3000")
ComboBox.ListFillRange = ws.Range(Rng)

I have also tried

ComboBox.ListFillRange = ws.Range("sheet2!A2:A3000")

However the combobox is not being populated, any suggestions?

Answer 1

The ListFillRange is a string, so you need to pass in the address of the range. And since the range is on another sheet, you need to qualify that address. Fortunately, the Address property has an External argument.

Sheet1.ComboBox1.ListFillRange = Sheet2.Range("A2:A3000").Address(, , , True)

The Address property will look like

?Sheet2.Range("A2:A3000").Address(, , , True)
[Book2]Sheet2!$A$2:$A$3000

But the control understands it and converts it.

?sheet1.ComboBox1.ListFillRange
Sheet2!$A$2:$A$3000

Having said that, I never use ListFillRange. I prefer to fill the control myself using List or AddItem.