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

Question

I have added a ListBox to a SHEET (not to a "UserForm") I did this using the mouse. I clicked the little Hammer and Wrench icon.

This ListBox seems to be easily referenced using code such as this:

ListBox1.Clear

or

ListBox1.AddItem("An option")

However, I have three of these ListBoxes (named, conveniently, ListBox1, ListBox2, and ListBox3) and I want to write a function to populate them with array data, like this:

Call populate_listbox(ListBox2, designAreaArray)

Where the first argument is the listbox name, the 2nd is the data.

But I do not know how to send "ListBox2" correctly, or refer to it correctly within the function.

For example:

Dim controlName as string
controlName = "ListBox1"

doesn't work, even if I define the function as follows:

Sub populate_listbox(LB As ListBox, dataArray As Variant)
    Dim i As Integer: i = 0
    For i = LBound(dataArray, 2) + 1 To UBound(dataArray, 2)    ' Skip header row
       LB.AddItem (dataArray(index, i))
    Next i
End Sub

Clearly it results in a mis-matched data type error. I've tried defining "controlName" as a ListBox, but that didn't work either...

Though perhaps it is my reference to the listBox that is incorrect. I've seen SO MANY ways to refer to a control object...

MSForms.ListBox.
ME.ListBox
Forms.Controls.
Worksheet.Shapes.

The list goes on an on, and nothing has worked for me.

Answer 1

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