CODEWITHSUNDEEP
excelvbacombobox
Chris
Chris

Reputation: 53

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

The form is not showing at all!

Upvotes: 1

Views: 16827

Answers (1)

FAB
FAB

Reputation: 2569

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

Upvotes: 2

Related Questions