Excel Populate a Combo Box from a Database

//

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.

Activate the MS ActiveX Data Objects 2.8 Library in the VBA Project References
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 

6 thoughts on “Excel Populate a Combo Box from a Database”

  1. 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).

    Reply
  2. 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 ?

    Reply
    • 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

      Reply
  3. 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.

    Reply
    • 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

      Reply
  4. 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?

    Reply

Leave a Comment