Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Populate a Userform Listbox using ADO

written by Will Riley - Last updated Oct 2004

The following code shows how we can use ADO to populate a Listbox positioned on an Excel Userform with data from an MS Access Database.

Required:

Access Database "Example.mdb"

Excel Book containing Userform1 and Listbox1

Userform code:

Private Sub UserForm_Initialize() 
    Call Populate_Listbox 
End Sub

Standard Module Code:

Sub Populate_Listbox() 
    '************************************************************************* 
    '* The following code populates a listbox on "Userform1" With data from  * 
    '* an MS Access Database ("Example.mdb")using ADO.                       * 
    '*                                                                       * 
    '* Requires references To the following:                                 * 
    '* 1. Microsoft ActiveX Data Objects 2.5 Or greater Library              * 
    '*                                                                       * 
    '* Special thanks To XL-Dennis For code & idea sharing ("2 heads > 1")   * 
    '************************************************************************* 
    Dim cnADO As ADODB.Connection 
    Dim rstADO As ADODB.Recordset 
    Dim strCon As String, strSQL As String 
    Dim vaData As Variant 
    Dim lCols As Long 
     
    'Set the ADO connection 
    Set cnADO = New ADODB.Connection 
    'Specify the connection string. 
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " _ 
    & "C:WillExample.mdb;Persist Security Info=False" 
    'Specify your SQL statement. 
    strSQL = "SELECT * FROM tblContacts" 
    With cnADO 
        .CursorLocation = adUseClient 
        'Client-side cursor location (As opposed To server-side) 
        'required As we are going To disconnect the recordset 
        'in order To populate the listbox. 
        .Open strCon 'Open the connection. 
        'execute the SQL statement. 
        Set rstADO = .Execute(strSQL) 
    End With 
     
    With rstADO 
        Set .ActiveConnection = Nothing 
        'Disconnect recordset. 
        lCols = .Fields.Count 
        'Populate a Variant array With the recordset. 
        vaData = .GetRows 
    End With 
    'Close the connection. 
    cnADO.Close 
    'Populate the Listbox. 
    With UserForm1 
        With .ListBox1 
            .Clear 
            .ColumnCount = lCols 
            .BoundColumn = lCols 
            .List = Application.Transpose(vaData) 
            .ListIndex = -1 
        End With 
    End With 
    'Release objects from memory. 
    Set rstADO = Nothing 
    Set cnADO = Nothing 
End Sub