Populating a ComboBox from VBA with 2 columns

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
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top