如何使用变量名称在工作表上引用控件对象？

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.

我已经将一个ListBox添加到SHEET（而不是“UserForm”）我用鼠标做了这个。我点击了小锤子和扳手图标。

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

这个ListBox似乎很容易使用如下代码引用：

ListBox1.Clear

要么

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:

但是，我有三个这样的ListBoxes（方便地命名为ListBox1，ListBox2和ListBox3），我想编写一个函数来用数组数据填充它们，如下所示：

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.

但我不知道如何正确发送“ListBox2”，或在函数内正确引用它。

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...

显然，它会导致错误匹配的数据类型错误。我已经尝试将“controlName”定义为ListBox，但这也不起作用......

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

虽然可能是我对listBox的引用不正确。我已经看到了很多方法来引用一个控制对象......

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

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

列表继续，没有任何对我有用。

2 个解决方案

#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

#2

Change the sub signature to match this:

更改子签名以匹配此：

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

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.

注意：这仅在您使用列表框的“ActiveX”版本时才有效。我假设你是因为你可以直接从模块调用ListBox1。

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.

PS：ActiveX控件是父表单对象的成员。因此，如果您在sheet1上有listbox1，您也可以像Sheet1.ListBox1一样调用它，这样如果您最终得到多个包含多个列表框的工作表，您就不会感到困惑。此外，您可能希望更改名称，以使自己更容易。

#1