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)
Add a reference to MS ADO Library x.x via Tools | Reference... in the VB-Editor.
Insert following code in the userform's module:
- Option Explicit
- Private Sub UserForm_Initialize()
- Dim wbBook As Workbook
- Dim wsSheet As Worksheet
- Dim rnData As Range
- Dim cnt As ADODB.Connection
- Dim rst As ADODB.Recordset
- Dim stDB As String, stSQL As String
- Dim vaData As Variant
- Dim j As Long, x As Long, i As Long
- Set wbBook = ThisWorkbook
- Set wsSheet = wbBook.Worksheets(1)
- Set cnt = New ADODB.Connection
- Set rst = New ADODB.Recordset
- stDB = ThisWorkbook.Path & "\Test.mdb"
- stSQL = "SELECT * FROM tblData"
- cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
- & "Data Source=" & stDB & ";"
- With rst
- .CursorLocation = adUseClient
- .Open stSQL, cnt, adOpenStatic, adLockReadOnly
- .ActiveConnection = Nothing 'Here we disconnect the recordset.
- j = .Fields.Count
- i = .RecordCount
- End With
- With wsSheet
- 'Write the fieldnames to the first row in the worksheet
- For x = 0 To j - 1
- .Cells(1, x + 1).Value = rst.Fields(x).Name
- Next x
- 'Dump the data to the worksheet.
- .Cells(2, 1).CopyFromRecordset rst
- 'Define the range to be used as RowSource
- Set rnData = .Range(.Cells(2, 1), .Cells(i, j))
- End With
- 'Adjust the columnwidth to be used in the listbox.
- rnData.Offset(-1, 0).Columns.AutoFit
- 'Here we manipulate the properties of the Listbox.
- With Me.ListBox1
- .BoundColumn = j
- .ColumnCount = j
- .ColumnHeads = True
- .ColumnWidths = rnData.Columns.Width
- .RowSource = rnData.Parent.Name & "!" & rnData.Address
- .ListIndex = -1
- End With
- Set rst = Nothing
- Set cnt = Nothing
- End Sub