使用数组元素在VBA中填充组合框 [英] populate combobox in VBA with array elements

问题描述

我有一个VBA程序（在Excel 2007中），我希望将一个组合框的ListFillRange属性设置为使用数组的列表。

我知道这个如果我右键单击组合框并在ListFillRange属性旁边写Sheet1！$ F2：$ F17，则可以使用。我也可以在代码中这样做。但是，我有兴趣通过为其分配一个数组来动态设置该属性的值。

我知道这个数组可以正常工作，这里可能有一个语法错误：

  ThisWorkbook.Worksheets（Sheet1）。OLEObjects（cmbS）。ListFillRange = ar 
  

当我这样做我得到：
类型不匹配错误。 >

此操作的结果应该是使用数组元素填充组件，从元素（0）...到数组的最后一个元素（n-1）。任何指针，非常感谢！

我也试过：

  ThisWorkbook.Worksheets（Sheet1）。cmbS.list = ar 
  

它说权限被拒绝

以下是组合框属性，以帮助它：

经过测试和尝试，我发现这样做：

  ThisWorkbook.Worksheets（Sheet1）。cmbS.ListFillRange =
 
 Dim i As Integer 
 For i = LBound（ar）To UBound ar）
 ThisWorkbook.Worksheets（Sheet1）。cmbS.AddItem（ar（i））
 
下一个
  

但是，我有兴趣一次性填充所有值，以获得更快的效果，不仅仅是添加元素。

解决方案

我知道它已经晚了，但也许会帮助别人。至少以下代码对我来说至少有效（比元素的元素快得多）。

  dim arr（）as variant 
 
 arr = Worksheets（Total）。Range（C2：& lrow）.Value 
工作表（Menu）。ComboBox2.List = arr 
  

I have a VBA procedure (in Excel 2007) where I aspire to set the ListFillRange property of a combobox styled as a list using an array.

I know this works if I right click the combobox and write "Sheet1!$F2:$F17" next to the "ListFillRange" property. I can also do this in code. However, I am interested in dynamically setting the value of this property by assigning it an array.

I know for sure the array works as I tested it; there is probably a syntax error here:

ThisWorkbook.Worksheets("Sheet1").OLEObjects("cmbS").ListFillRange = ar

when I do this I get: "Type mismatch" error.

The result of this action should be that the component is populated with the array elements, from element(0) ... to the last element (n-1) of the array. Any pointers, thank you very much!

I also tried:

ThisWorkbook.Worksheets("Sheet1").cmbS.list = ar

and it says "permission denied"

Here are the combobox properties in case it helps:

After testing and trying, I found this works:

ThisWorkbook.Worksheets("Sheet1").cmbS.ListFillRange = ""

Dim i As Integer
For i = LBound(ar) To UBound(ar)
    ThisWorkbook.Worksheets("Sheet1").cmbS.AddItem (ar(i))

Next

However, I am interested in populating with all values at once for faster effect, not just adding element by element.

解决方案

I know its late but maybe it is going to help someone else. At least the following code works (much faster than element for element) for me.

dim arr() as variant

arr = Worksheets("Total").Range("C2:"&lrow).Value
Worksheets("Menu").ComboBox2.List = arr

这篇关于使用数组元素在VBA中填充组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！