Dynamically populate a listbox in XL from a database

  • In this example I will show how we can populate a listbox in a userform with data from a database.


    Since we don´t know from time to time the number of required fields and the necessary width of the columns we need to have a dynamic solution which reflect this situation.


    In addition we also require that the fieldnames should be shown in the listbox. The only way to use columnsheads is to use the property RowSource of the listbox. This require that the the fieldnames as well as the data are written to a worksheet before populating the listbox.


    In order to maintain an acceptable performance the recordset in the example is disconnected.


    The solution make it also possible to handle Null-values or other consideration we may want in a more flexibel way then what is offered via the SQL-statement as we can apply Excel-techniques on the data.


    What we need:
    * MS Windows 2000 or above
    * MS Excel 2000 or above
    * MDAC 2.5 or above (ADO Library)


    Step 1
    Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.


    Step 2
    Insert following code in the userform's module:


    Kind regards,
    Dennis