如何使用变量名称引用工作表上的控件对象？ [英] How do I refer to a controls object, on a worksheet, using a variable name?

问题描述

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

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")

但是，我有三个这样的列表框（名称，方便，ListBox1，ListBox2和ListBox3），我想编写一个函数来填充数组数据，如下所示：

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.

但是我不知道如何正确发送ListBox2或者在函数内正确地引用它。

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

例如：

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

很明显，它会导致错误匹配的数据类型错误。我已经尝试将controlName定义为ListBox，但是没有起作用。

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.

推荐答案

尝试这样：

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

这篇关于如何使用变量名称引用工作表上的控件对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！