How can I refer to a control object in a worksheet using the variable name?

Question

I added the ListBox to the sheet (and not to the "UserForm"). I did this with the mouse. I clicked on the small hammer and wrench icon.

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

ListBox1.Clear 

or

 ListBox1.AddItem("An option") 

However, I have three of these lists (names, conveniently, ListBox1, ListBox2 and ListBox3), and I want to write a function to populate them with array data, for example:

 Call populate_listbox(ListBox2, designAreaArray) 

If the first argument is the name of the list, then the second is the data.

But I do not know how to send "ListBox2" correctly or refer to it correctly.

For instance:

 Dim controlName as string controlName = "ListBox1" 

does not 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 

It is clear that this leads to an incorrect data type error. I tried to define "controlName" as a ListBox, but that didn't work either ...

Although, perhaps this is an invalid link to the listBox. I saw many ways to access the control object ...

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

The list is on and nothing worked for me.

Answer 1

Try the following:

 Dim cMyListbox As MSForms.ListBox Set cMyListbox = Sheet1.ListBox1 '// OR Worksheets("YourSheetName").Listbox1 cMyListbox.AddItem("An option") 

You can also populate the list without having to iterate over the array, try the following:

 Dim cMyListbox As MSForms.ListBox Dim vArray As Variant Set cMyListbox = Sheet1.ListBox1 vArray = Range("A1:A6").Value cMyListbox.List = vArray 

Answer 2

Change the sub-letter to match:

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

Now you can pass it as you tried initially.

NOTE This only works if you used the ActiveX list version. I assume you are, because you can call ListBox1 directly from the module.

PS: ActiveX controls are members outside the parent sheet object. Therefore, if you have listbox1 on sheet1, you can also name it as Sheet1.ListBox1 so as not to get confused if you end up with multiple sheets with multiple lists. Alternatively, you can change the name just to make it easier.

Answer 3

 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 

Answer 4

Unfortunately, your solution does not meet my needs. I have 10 ActiveX buttons, names tbnLong1, tbnLong2, etc.

I would like to transfer control over their actions and properties (Caption, Color) to one procedure, passing only serial number 1,2,3, etc.

as part of the procedure, I would like to call them "tbnLong" & 1, 2,3 ... Until now, without success.

Using controls ("tbnLong" & #) or Shapes ("tbnLong" & #) does not work.

Shapes (1) works, but I will need to go through all 40 controls on this sheet to get the correct name.