Populate Combobox with Two Text Fields from an Array

Question

I'm trying to get values from 2 cells in an array to populate the text field of a combobox on a userform. The values in the array look like this:

A  1
B  2
C  3
B  4

I would like to make a distinction between B2 and B4 in the combobox text field. The field currently populates like B. I would like it to populate with B 2 instead. This question is partly related to this question.

I tried using this that was linked from here but couldn't get the ListCount property to work. I used this to better understand arrays in a hope that it would provide some insight. I tried this approach but it doesn't populate the text field. WhereInArray looks like it is more for finding a value and also appears to be unique-value-dependent. I tried this but it seems to get tripped up on the array. Any help would be greatly appreciated.

score 1 · Accepted Answer · answered May 08 '19 at 14:47

Imagine this data in Worksheets("Sheet1")

Populate your combobox ComboBox1 like this: Loop through the data and use .AddItem to add a combination of both data columns.

Private Sub UserForm_Initialize()
    Dim Data() As Variant 'array
    Data = Worksheets("Sheet1").Range("A1:B4").Value 'read data into array

    Me.ComboBox1.Clear

    Dim iRow As Long
    For iRow = LBound(Data, 1) To UBound(Data, 1)
        Me.ComboBox1.AddItem Data(iRow, 1) & " " & Data(iRow, 2)
    Next iRow
End Sub

Then you can select your item as follows:

And you can retrieve the value B 2 with Me.ComboBox1.Text:

Debug.Print Me.ComboBox1.Text 'returns B 2

Populate Combobox with Two Text Fields from an Array

1 Answers1

Linked