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.

Reafidy · Accepted Answer · 2012-01-19T03:01:59.760

7

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

edited Jan 19 '12 at 03:01

answered Jan 19 '12 at 02:26

Reafidy

8,240
5
51
83

Thank you. I modified my sub per justnS's answer, and set my variable (cMyListbox) as you indicated above, and the combination worked. Thanks for the tip regarding filling the list, also. – supermitch Jan 19 '12 at 17:06

Justin Self · Answer 2 · 2012-01-19T03:26:07.487

3

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.

edited Jan 19 '12 at 03:26

answered Jan 19 '12 at 03:20

Justin Self

6,137
3
33
48

Thanks for the response. I modified my variable declaration per Reafidy's answer, and modified my sub according to yours, and it works now. – supermitch Jan 19 '12 at 17:07

score 0 · Answer 3 · answered Aug 19 '18 at 03:37

Dim controlName As OLEObject
    Set controlName = Sheet1.OLEObjects("ListBox1")

Call populate_listbox(controlName, designAreaArray)

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

score 0 · Answer 4 · answered Dec 25 '20 at 16:43

0

To access the state of a checkbox Active-X control on Sheet1:

Dim checkBox1 As Object
Set checkBox1 = Sheet1.OLEObjects("CheckBox1").Object
MsgBox checkBox1.Value

answered Dec 25 '20 at 16:43

LeslieM

2,105
1
17
8

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

4 Answers4

Linked