CODEWITHSUNDEEP
excelvbalistbox
Josh
Josh

Reputation: 265

Listbox ListFillRange

I have inserted a form control listbox on my sheet. For some reason this errors out.

Sheet1.Shapes("ListBox1").ListFillRange = "A1:A10"

I'd like to fill the listbox with the range but I get a Run-time error '438', Object doesn't support this property or method. What am I missing here?

Upvotes: 2

Views: 11373

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

If it is Form listbox, use this one:

Worksheets("Sheet1").Shapes("ListBox1").ControlFormat.ListFillRange = "A1:A10"

or with Range object:

With Worksheets("Sheet1")
    .Shapes("ListBox1").ControlFormat.List = .Range("A1:A10").Value
End With

Upvotes: 3

Starscream1984
Starscream1984

Reputation: 3062

You'll want to use something more like:

Sheet1.ListBox1.ListFillRange = "A1:A10"

You were trying to find a Shape object called "ListBox1" and then run a method that Shape objects don't have.

Edit v2: I see, in that case a cast would be needed (and they work differently from vb.NET), I think this could work:

Dim myListBox as ListBox
SET myListBox = Sheet1.Shapes("ListBox1")
myListBox.ListFillRange = "A1:A10"

Upvotes: 0

Related Questions