VBA UserForms:
Fill a Combo Box or List Box List from a Database

There are different ways to create the list of items displayed by a combo box or a list box on a VBA UserForm. One way is to "hard code" the list into the UserForm's Initialize event procedure using the .AddItem method. This is fine if you know what the contents of the list should be, and if is not going to change regularly.

In Excel, you can set the control's RowSource property to a range of cells containing the list (the best way is to name the range and enter that name as the property value). This also allows you to change the list if you need to without having to edit the VBA code. You can even use a dynamic range name so that you don't have to redefine the range each time you add a new item.

But if you are working in a program other than Excel you have to generate the list with code. A UserForm in Word or PowerPoint doesn't have a range of cells it can refer to. And even if you are working in Excel maybe you would like to get your list from somewhere else.

This tutorial explains how to build a UserForm's combo box or list box list (they are both treated the same way) by retrieving the list items from a table in an Access database.

Set a Reference to ADO

Let's assume that you have a UserForm to which you have added a combo box or a list box, and that you also have a database that contains a table from which you can retrieve the list items. The code that retrieves the information from the database uses ADO (ActiveX Data Objects). This is a subset of the Visual Basic programming language specifically designed for communicating with databases. Microsoft Access, being a database program, already knows about ADO but if you are using any other Microsoft Office program you have to set a reference to ADO so that your program knows how to speak to the database.

In the Visual Basic Editor go to Tools > References to open the References dialog. In the list of Available References you will see that some already have a tick against them. Unless ADO is already selected, scroll down the list and find the entry for Microsoft ActiveX Data Objects 2.x Library (where x is the highest available number - unless you are programming for an earlier version of Office). Place a tick in the adjacent checkbox and click the OK button...

If you reopen the References dialog you will see that the ADO reference has moved to join the other selected ones near the top of the list.

Collect the Information You Need

Since the code needs to interact with the database file it needs to know the exact path and filename. As you will see below it uses this to create a Connection Stringto open a connection to the database. The Connection String also specifies the appropriate driver to use. This example is appropriate for a Microsoft Access database. If you are working with something else (such as a database on Microsoft SQL Server) you will have to make changes. Search for help on ADO Connection Strings to find out what to use. Here is a typical example of a connection to an Access database:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=C:\Databases\StaffDatabase.mdb"

After successfully connecting to the database ADO uses an SQL statement to open a recordset which is held in the computer's memory. Even if your database table contains just a single field containing each of the list items in the order you want them, you still have to use an SQL statement to build the recordset. The SQL statement I use in this example retrieves a unique list of Department names from a field named Department contained in a table called tblStaff. I have also chosen to sort the list into ascending alphabetical order:

"SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];"

If you are not confident to write your own SQL statement you can use the query tool in Access to create a query that returns the list you need, then copy the resulting SQL from the query's SQL View.

Write the ADO Code

The code should be placed in the UserForm's Initialize event procedure. This event fires each time the form is opened so the list will always be up-to-date. If necessary, right-click the UserForm and choose View Code to open its code module then choose UserForm and Initialize from the drop-down lists (left and right respectively) at the top of the code window to create an empty procedure. The finished code, tailored to your own requirements, should look like this:

Private Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\Databases\StaffDatabase.mdb"
    rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![Department]
            rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

Remember to edit the cnn.Open and rst.Open statements to suit your own requirements. Change the name of the combo box or list box to match yours (here it is called ComboBox1), and enter the name of the field that contains the list items into the AddItem statement. If you have coded everything correctly your UserForm will build the list as it opens:

Note that I have included an error handler and exit routine into the code. It is good practice to include an error handler in any procedure during which something might go wrong. This is particularly important when working with databases.

How the Code Works

Following the error handling instruction and the necessary variable declarations, the procedure starts by opening a connection to the database. It then opens a recordset based on the supplied SQL statement and moves to the first record. It clears any existing items from the combo box list then proceeds to loop through the recordset. For each record it adds a new item to the combo box list, getting the item's value from the specified field (here it is the Department field) before moving to the next record. When the loop reaches the end of the recordset (EOF = End Of File) it closes the recordset and the connection connection to the database then sets their variables to Nothing to clear the computer's memory.

Filling a Multi-Column List

The code required to create a multi-column list is slightly different. Remember to set the ColumnCount and ColumnWidths properties of your combo box or list box to the appropriate values. You will also need to modify the SQL statement to return as many columns as you need.

In this example two fields (Code and Country) are brought from a table named tblISOCountryCodes. I have also declared a variable i to act as a counter to keep track of them index number of each row as it is added to the list. This listing shows how the code differs from the previous example (the unchanged code is not shown):

Dim i As Integer
rst.Open "SELECT [Code], [Country] FROM tblISOCOuntryCodes ORDER BY [Country];", _
         cnn, adOpenStatic
rst.MoveFirst
i = 0
With Me.ListBox1
    .Clear
    Do
        .AddItem
        .List(i, 0) = rst![Code]
        .List(i, 1) = rst![Country]
        i = i + 1
        rst.MoveNext
    Loop Until rst.EOF
End With

As the procedure loops through the recordset it adds a new item to the list as before, but this time, since it has to write into several columns, it is a bit more complicated. The .AddItem method adds a new empty row to the list but, unlike the previous example, does not specify what it contains. The variable i is keeping track of the index number of each new row. The .List property of the (in this example) list box is then set for each column. It gives the row index (i) and the column index (0, 1, etc. numbering from zero) and the value to be written into the list (the appropriate field from the recordset). Before moving to the next record the value of i is incremented by one ready for the next item.

This example has just two columns but you can have as many as you like. Remember to set the control's properties to accept the additional columns, adjust the SQL statement to return the required number of fields and add an extra .List statement for each additional column.