using combo

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 String to 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.