Populating a ComboBox from VBA with 2 columns

  • Thread starter Thread starter df
  • Start date Start date
D

df

Hi I need to populate an excel combo box with recordset
fields i already retrieve from an SQL Database. I need to
columnes code and description. For example: Employee ID
and Employee Lastname. Y need to show the Lastname in the
combo and retrive the selected Employee ID from it.

I tried
ComboBox.AddItem()

But I could only add an item with one field. How can I add
the couple Employee ID - LastName? (I discard the
concatenation method).

Regards
Daniel
 
If those values are on a worksheet in adjacent columns, you could pick up that
range and dump it into an array. Then use the array to fill the combobox:

Option Explicit
Private Sub UserForm_Initialize()

Dim iRow As Long
Dim myArray As Variant

myArray = Worksheets("sheet1").Range("a2:b20")

With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2
.ColumnWidths = ".5 in; .5 in"
.List = myArray
End With

End Sub

Since I set the boundcolumn to 2, I get the second column returned. (You could
even hide the second column by using: .ColumnWidths = ".5 in; .0 in"

But the value is determined by the .boundcolumn.

Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.Value
End Sub

If you can't pick it up nicely, you can add them one at a time:

Private Sub UserForm_Initialize()

Dim iRow As Long
Dim myCell As Range

With Me.ComboBox1
.ColumnCount = 2
.BoundColumn = 2
For iRow = 1 To 20 step 3
Set myCell = Worksheets("sheet1").Cells(iRow, 1)
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 1).Value
Next iRow
End With

End Sub

Another option may be to just put the one column in and then use some kind of
lookup to determine the other value.
 
Back
Top