How do I refer to a controls object, on a worksheet, using a variable name?

Change the sub signature to match this:

Sub populate_listbox(LB As MSForms.ListBox, dataArray As Variant)

Now you can pass it like you were trying to originally.

NOTE: This only works if you used the "ActiveX" version of the listbox. I'm assuming you are because you are able to call ListBox1 straight from a module.

PS: The ActiveX controls are members off of the parent sheet object. So if you have the listbox1 on sheet1, you can also call it like Sheet1.ListBox1 so you don't get confused if you end up with multiple sheets with multiple listboxes. Also, you may want to change the name just to make it easier on yourself.


Try this:

Dim cMyListbox As MSForms.ListBox

Set cMyListbox = Sheet1.ListBox1  '// OR Worksheets("YourSheetName").Listbox1

cMyListbox.AddItem("An option")

Also you can populate a listbox without having to loop through the array, try this:

Dim cMyListbox As MSForms.ListBox
Dim vArray As Variant

Set cMyListbox = Sheet1.ListBox1

vArray = Range("A1:A6").Value
cMyListbox.List = vArray

Tags:

Excel

Vba