从表格填充组合框 [英] Populate ComboBox from Table
问题描述
我正在尝试填充一个两列组合框,该组合框是我在用户表单中使用的,它从表中获取数据.组合列表必须是动态的,因此当其他项目添加到表中时,它们将被馈送到组合框中.
I'm trying to populate a two column combo box I am using in a user form taking the data from a Table. The combo list needs to be dynamic so when additional items are added to the Table they are fed into the combination box.
我正在使用事件之前双击来初始化代码,但是表单没有初始化.表格已使用更简单的方法进行了初始化,该方法从固定范围内获取数据-表格让我失望.
I'm using a before event double click to initiate the code but the form is not initialising. The form has initialised using a simpler approach taking data from a fixed range - the Table is throwing me.
我已经分别运行了代码,并且可以看到组合列表数组已被填充.
I have run the code separately and I can see the combolist array getting populated.
我尝试重新组合列表数组,但是出现一个错误,表明它已经变暗了.
I have tried to redim the combolist array but an error comes up saying it is already dim'd.
我怀疑我过于繁琐了从表中填充组合框的代码.
I suspect I'm overcomplicating the code to populate the combobox from a Table.
Private Sub UserForm_Initialize()
'Populate Combo list values
ComboBox1.ColumnCount = 2
Dim myTable As ListObject
Dim myArray As Variant
Dim x As Long, NumItems As Long
Set myTable = Worksheets("RefTable").ListObjects("AccountTable")
myArray = myTable.DataBodyRange
NumItems = UBound(myArray)
Dim ComboList(1 To 20, 1 To 2) As String
Dim i As Integer, j As Integer
For i = 1 To 20
For j = 1 To 2
ComboList(i, j) = myArray(i, j)
Next j
Next i
ComboBox1.List = ComboList
End Sub
该表单完全不显示!
推荐答案
您与代码非常接近,我刚刚对数组进行了更改:
You are pretty close with your code, I've just made changes around your arrays:
Private Sub UserForm_Initialize()
'Populate Combo list values
ComboBox1.ColumnCount = 2
Dim myTable As ListObject
Dim myArray As Variant
Dim i As Long, j As Long
Set myTable = Worksheets("RefTable").ListObjects("AccountTable")
myArray = myTable.DataBodyRange
Dim ComboList() As String: ReDim ComboList(1 To UBound(myArray), 1 To UBound(myArray, 2))
For i = LBound(ComboList) To UBound(ComboList)
For j = LBound(ComboList, 2) To UBound(ComboList, 2)
ComboList(i, j) = myArray(i, j)
Next j
Next i
ComboBox1.List = ComboList
End Sub
或者,您可以只使用第一个数组:
Alternatively you can just use the first array:
Private Sub UserForm_Initialize()
'Populate Combo list values
ComboBox1.ColumnCount = 2
Dim myTable As ListObject
Dim myArray As Variant
Set myTable = Worksheets("RefTable").ListObjects("AccountTable")
myArray = myTable.DataBodyRange
ComboBox1.List = myArray
End Sub
这篇关于从表格填充组合框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!