Populate UserForm ListBox

The information in this website is provided without risk or obligation and free of charge. However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal. Thank you.

This Microsoft Word Help and Tips Page demonstrates several methods that you can use to populate UserForm ListBoxes. Fellow Microsoft Word MVP Doug Robbins kindly assisted with the preparation of this page. Thanks Doug! Simple List ListBoxes provide users with a compact and convenient means of selecting one or more pre-defined text segments. In its simplest form, a ListBox could be used to for responding to "yes" or "no" questions.

The code for populating a Listbox is normally placed in the UserForm Initialize procedure. The "AddItem" method is well suited for a short simple list.

The "AddItem" method becomes cumbersome as the number of list members gets larger. For example, the list of U.S. state abbreviations would require 52 separate lines of code to populate. Fortunately you can use the more versatile "List" method and an array of data to simplify the job.

Multi-column List A ListBox can have multiple columns of data. You use the "ColumnWidths" property to set the width of the individual columns. A zero width column results in a column of hidden data. For example, lets create a Userform for entering a U.S. address. The user will select his or her state from a list of state names. We want to insert the state abbreviation (stored in a hidden column) that corresponds with the user selection in the address field of the document.

The code for populating the ListBox in the illustration above and displaying the result in the document (shown on the right) uses several properties, methods, and functions of the ListBox control. These are described in the illustration below.

External Data Sources

Each of the previous examples used data contained in the UserForm Initialize event to create the ListBox list members. Next we will look at some methods for using an external data source to populate a ListBox.

The first method uses a Microsoft Word table contained in a separate document as the external source. Follow Word MVP Doug Robbins has posted this method regularily in the Microsoft Word Public newsgroups. The next series of illustrations show an example of the source file table, how the source data is displayed in the Userform, how the selected list item is displayed in the document, and the code for populating the ListBox and displaying the results. For the example, I used a source document saved as "E:\Junk\sourceWord.doc"

The source document table:

This information will be displayed in the UserForm as:

The selected client address is displayed in the document as:

Code for storing the information in the source table as a zero based two dimensional array and then populating the ListBox using the List property and the array is shown below:

The data displayed in the document is assembled in the UserForm CommandButton1_Click procedure. Each element of the data is taken from one of the individual ListBox1 columns. The ListBox BoundColumn and Value properties are employed to assemble the data as shown below:

The next series of illustrations show several methods for populating a ListBox with external data from an Access DataBase or an Excel Spreadsheet. Each of the examples can be used to populate a multi-column ListBox. The Access and Excel source files I used are named E:\Junk\sourceAccess.mdb and E:\Junk\sourceExcel.xls respectively. Each file contains fields for Employee Name, Employe DOB, and Employee ID. You will need to modify the code shown to reflect your actual file name and path.

Access Example 1

Private Sub UserForm_Initialize()
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
'Open the database to retrieve data
Set myDataBase = OpenDatabase("E:\Junk\sourceAccess.mdb")
'Define the first recordset
Set myActiveRecord = myDataBase.OpenRecordset("Table1", dbOpenForwardOnly)
'Set the listbox column count
ListBox1.ColumnCount = myActiveRecord.Fields.Count
i = 0
'Loop through all the records in the table until the EOF
'marker is reached. Use AddItem to add a new row for each record.
'Use List to populate the ListBox column and row field.
Do While Not myActiveRecord.EOF
    ListBox1.AddItem
    ListBox1.List(i, 0) = myActiveRecord.Fields("Employee Name")
    ListBox1.List(i, 1) = myActiveRecord.Fields("Employee DOB")
    ListBox1.List(i, 2) = myActiveRecord.Fields("Employee ID")
    i = i + 1
'Get the next record
    myActiveRecord.MoveNext
Loop
'Close the database and clean-up.
myActiveRecord.Close
myDataBase.Close
Set myActiveRecord = Nothing
Set myDataBase = Nothing
End Sub

Access Example 2

Private Sub UserForm_Initialize()
'You need to set a reference in your project to the “Microsoft DAO 3.51 (or 3.6) Object Library”.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
'Open the database to retrieve data
Set db = OpenDatabase("E:\Junk\sourceAccess.mdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT * FROM Table1")
'Determine the number of records in the recordset
With rs
    .MoveLast
    NoOfRecords = .RecordCount
    .MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
ListBox1.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Excel Example 1

Private Sub UserForm_Initialize()'
'You need to set a reference in your project to the ''“Microsoft DAO 3.51 (or 3.6) Object Library”.
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Open the spreadsheet containing the data
Set db = OpenDatabase("E:\Data Stores\sourceSpreadsheet.xls", False, False, "Excel 8.0")
'Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
'Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
'Determine the number of retrieved records
With rs
    .MoveLast
    i = .RecordCount
    .MoveFirst
End With
'Load the combobox with the retrieved records
ListBox1.Column = rs.GetRows(i)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Excel Example 2

Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim cRows As Long
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
'Open the spreadsheet to get data
Set xlWB = xlApp.Workbooks.Open("E:\Data Stores\sourceSpreadsheet.xls")
Set xlWS = xlWB.Worksheets(1)
cRows = xlWS.Range("mySSRange").Rows.Count - _
xlWS.Range("mySSRange").Row + 1
ListBox1.ColumnCount = 3
'Populate the ListBox. Use AddItem to add a new row for each record.
'Use List to populate the ListBox column and row field.
With Me.ListBox1
    For i = 2 To cRows
        .AddItem xlWS.Range("mySSRange").Cells(i, 1)
        .List(.ListCount - 1, 1) = xlWS.Range("mySSRange").Cells(i, 2)
        .List(.ListCount - 1, 2) = xlWS.Range("mySSRange").Cells(i, 3)
    Next i
End With
'Clean up.
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

The source data in the four examples above will appear in the UserForm similiar to the illustration below (I.e., depending on the data in your source file):

Cascading ListBoxes

Now I want to show you a method for populating secondary and teriary ListBoxes based on the value chosen in a parent primary ListBox. For example, the UserForm shown below has three list boxes. The list displayed in ListBox2 is determined by the user selection in ListBox1. The list displayed in ListBox3 is determined by the user selection in ListBox2.

The data source for the UserForm ListBoxes shown above is contained in a Microsoft Word table as shown in the illustration below. Non-printing characters are displayed so you can see that the ListBox1 members are taken from a unique single cell as seen on the left. The ListBox2 members are a taken from unique single paragraph in a cell associated with the manufacturer cell (i.e., in the same row). ListBox3 members are taken from a grouping of members separated by the pipe "|" symbol contained in an individual unique paragraph associated with the paragraph of the catagory members (i.e., the Desktop category and Desktop models are both listed in paragraph 1 or thier respective cells).

All of the data shown below is loaded in ListBox1 when the Userform initializes. We use the ListBox1.ColumnWidths property to hide the display of the category and model data. All the user sees initially is the manufacturer list.

The UserForm Initialize code is shown below.

When the user selects a PC manufacture we use the ListBox1_Change event process the code that populates ListBox2

When the user selects a category we use the ListBox2_Change event process the code that populates ListBox3

The following code is provided as an example for processing the data selected:

With a lot more work and tedious attention to detail in creating the source document and UserForm code you can continue cascading listboxes practically indefinately. Here is an "abbreviated" exampe (i.e., my source document is incomplete) of listboxes that cascade seven levels. If you are interested in something this deep then contact me via the website feedback.

Multi-Select ListBox

This last piece is provided to help you understand and use the ListBox Selected property. ListBoxes can be configured to allow single or multiple item selection. In this example the user is asked to choose their two favorite sports from a list. Code in the ListBox_Change event and the UserForm Command Button_Click event ensure that two and only two selections are made and processes the results.

A counter in the ListBox_Change event counts the number of selected list members and prohibits selecting more than two items.

The CommandButton_ Click event requires two selections before processing the result

That's it . I hope you have found this Tips Page helpful and informative. Click on the following hyperlink to download a file containing these macros and UserForms: Load UserForm Listbox.doc

Looking for something else?