To populate a combo box on a userform in Excel you need to use VBA. Firstly add a combo box to a userform in the VBA window (Alt-F11) of your spreadsheet.
The VBA code below shows how to populate a 2 column combo box. Add this code to the Userform Initialize event to populate the combo box when the form is loaded.
You might need to add a Reference to make the ADODB part of this work. Go to Tools – References in the VBA window and find the highest number Microsoft ActiveX Data Objects 2.X Library. Select the highest 2.X library.
Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim i As Integer
conn.Open "Initial Catalog=database;Data Source=server;"""
rst.Open "select distinct Field1, Field2 from table order by Field1", conn, adOpenDynamic
i = 0
rst.MoveFirst
With Me.ComboboxName
.Clear
Do
.AddItem rst!Field1
.List(i, 0) = rst!Field1
.List(i, 1) = rst!Field2
i = i + 1
rst.MoveNext
Loop Until rst.EOF
End With
UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub
Thanks for this post. I has truly helped me accomplish my task great deal. There is however one aspect that’s not working well. The ComboBox only displays “Field1”. I would like it to display “Field1” in column1 and “Field2” in column2. While the bound column remains as Column1 (Index field).
I managed to resolve the issue mentioned above. There is another for which I would like your assistance. How can I allow the “Auto Complete” option in the Combo Box ?
This is in the combo box properties:
1. In the Properties window, click in the MatchEntry property
2. From the dropdown list, select 1-frmMatchEntryComplete
Thanks for this great post.
I have a program which I think your code will help me a great deal to accomplish however, I only need one column of the table in the database to get into the combo box and also when I tried to copy part of your code and resuse, it was returning an error on ‘AddItem rst!Field1’ line.
Please any idea on how to go about this.
If you alter your SQL to only return one field, then alter the VBA in the WITH statement to the following it should work:
With Me.ComboBoxName
.Clear
Do
.AddItem rst!Field1
rst.MoveNext
Loop Until rst.EOF
End With
how can i do it if i have more then one Combox for example
With Me.ComboBox1 & Me.ComboBox1
.Clear
Do
.AddItem rst!Field1
rst.MoveNext
Loop Until rst.EOF
End With
Would That work?