So this is for a purchase order data entry application where the user enters each item from a PO into the UserForm listbox with different attributes of the item in each column. For example:
Column 1: Name of the product Column 2: Qty purchased Column 3: Price
Everything works great, but I cannot get the values out of the 0+n columns in the list box. I've only found this code, but it only gets me the text value for the nth column:
ListBox1.ListIndex = n MsgBox ListBox1.TextI can get the value for [n, column 0], which I need. But I also need the value of [n, column 1], [n, column 2], etc.
Help!!!
Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.
This Microsoft Word Tips & Microsoft Word Help page demonstrates several methods that you can use to populate a userform listbox [or combobox]. Microsoft Word MVP Doug Robbins has kindly assisted with the preparation of this page. Thanks Doug!
1. See my Create & Employ a Userform tips page for information on creating and employing userforms. 2. The basic process for populating a listbox or combobox is the same. For brevity, I will use one term or the other in the examples that follow. 3. You can download the demonstration document containing all of the example userforms and VBA procedures used to prepare this tips page with the link at the end of the page.
Review
A listbox provides users with a convenient means of selecting one or more items from a fixed pre-defined list.
A combobox provides users with a compact and convenient means of selecting a single item from a pre-defined list. A combobox can be configured to accept pre-defined list entries only or allow the user to enter his or her own text.
Rows [and columns in multi-column] listboxes are indexed starting with 0. For example the .ListIndex property returned if the first item in a listbox is select is 0. This can be confusing as the numbering used in with some of the other properties [e.g., .ColumnCount, .TextColumn, etc.] begin with 1.
Simple List
In its simplest form, a listbox could be used for responding to simple 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 and District of Columbia abbreviations would require 51 separate lines of code to populate. Fortunately you can use the more versatile "List" method and an array of data to simplify the job.
VBA Userform Script
Private Sub UserForm_Initialize[] Dim myArray[] As String 'Use Split function to return a zero based one dimensional array. myArray = Split["AL|AK|AZ|AR|CA|CO|CT|DE|DC|FL|" _ & "GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|" _ & "MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|" _ & "NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|" _ & "TX|UT|VT|VA|WA|WV|WI|WY", "|"] 'Use .List method to populate listbox. ListBox1.List = myArray lbl_Exit: Exit Sub End Sub
Multi-column List
A listbox can list and display multiple columns of data.
- Use the ".ColumnWidths" property to set the width of the individual columns.
- A zero width column results in a column of hidden data.
In the example below the listbox displaying the state full name has second hidden column containing the state abbreviation. The user will select his or her state name from the list but the result in the document will be the state abbreviation.
The code for populating the listbox shown above and for displaying the result in the document is provided and explained below:
VBA Userform Script:
Option Explicit Private Sub UserForm_Initialize[] Dim arrStateName[] As String Dim arrStateAbbv[] As String Dim i As Long 'Use the Split function to create two zero based one dimensional arrays. arrStateName = Split["Select State|Alabama|Alaska|Arizona|" _ & "Arkansas|California|Connecticut|Etc.", "|"] arrStateAbbv = Split[" |AL|AK|AZ|AR|CA|CT|Etc", "|"] 'Use the .ColumnWidth property to set column widths. 0 results in a hidden column. ListBox1.ColumnWidths = "60;0" For i = 0 To UBound[arrStateName] 'Use the .AddItem method to add a multi-column row for each array element. ListBox1.AddItem 'Use .List method to write array data to specific listbox row and column. ListBox1.List[i, 0] = arrStateName[i] ListBox1.List[i, 1] = arrStateAbbv[i] Next i lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click[] Dim oRng As Word.Range Dim oBM As Bookmarks 'Write userform data to bookmarked ranges in the document. Set oBM = ActiveDocument.Bookmarks Set oRng = oBM["Address"].Range oRng.Text = TextBox1.Text oBM.Add "Address", oRng Set oRng = oBM["City"].Range oRng.Text = TextBox2.Text oBM.Add "City", oRng Set oRng = oBM["State"].Range 'Use the listbox hidden column data. Note columns are indexed _ starting with 0. oRng.Text = ListBox1.Column[1] oBM.Add "State", oRng Set oRng = oBM["Zip"].Range oRng.Text = TextBox3.Text oBM.Add "Zip", oRng Me.Hide Set oRng = Nothing Set oBM = Nothing lbl_Exit: Exit Sub End Sub
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.
Word table
The first method uses a Microsoft Word table contained in a separate document as the external source. Word MVP Doug Robbins has posted this method regularly in the Microsoft Word Public newsgroups.
Example 1 - The following 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.
- The code for populating the listbox and displaying the results
For this example, I used a source document saved as "D:\Data Stores\sourceWord.doc"
The results
The code for populating the listBox shown above and for displaying the result in the document is provided and explained below:
VBA Userform Script:
Option Explicit Private Sub UserForm_Initialize[] Dim arrData[] As String Dim sourcedoc As Document Dim i As Integer Dim j As Integer Dim myitem As Range Dim m As Long Dim n As Long Application.ScreenUpdating = False 'Modify the following line to point to your list member file and open the document Set sourcedoc = Documents.Open[FileName:="D:\Data Stores\sourceWord.doc", Visible:=False] 'Get the number of list members [i.e., table rows - 1 if header row is used] i = sourcedoc.Tables[1].Rows.Count - 1 'Get the number of list member attritbutes [i.e., table columns] j = sourcedoc.Tables[1].Columns.Count 'Set the number of columns in the Listbox ListBox1.ColumnCount = j 'Load list members into an array ReDim arrData[i - 1, j - 1] For n = 0 To j - 1 For m = 0 To i - 1 Set myitem = sourcedoc.Tables[1].Cell[m + 2, n + 1].Range myitem.End = myitem.End - 1 arrData[m, n] = myitem.Text Next m Next n 'Use the .List property to populate the listbox with the array data ListBox1.List = arrData 'Close the source file sourcedoc.Close SaveChanges:=wdDoNotSaveChanges lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click[] Dim i As Integer Dim Client As String Dim oRng As Word.Range Client = "" For i = 1 To ListBox1.ColumnCount 'Set the .BoundColumn property. Note .BoundColumn indexed starting at 1. ListBox1.BoundColumn = i 'Use .Value property to get data from listbox bound column. Select Case True 'Build the address display Case i = ListBox1.ColumnCount - 1 Client = Client & ListBox1.Value & " " Case i = ListBox1.ColumnCount Client = Client & ListBox1.Value & vbCr Case Else Client = Client & ListBox1.Value & vbCr & vbTab End Select Next i Set oRng = ActiveDocument.Bookmarks["Client"].Range oRng.Text = Client ActiveDocument.Bookmarks.Add "Client", oRng Me.Hide lbl_Exit: Exit Sub End Sub
With userform controls there are often more than one way to achieve a desired result. With the User Address form in the multi-column list example I used the .Column property to return data from the user selection. The .Column property is, in my opinion, the easiest method. In the example above, for demonstration purposes, I used a combination of the .BoundColumn and .Value properties of the listbox to return the data.
- The .BoundColumn property identifies the source of data in a multi-column listbox or combobox.
- The .Value property returns the content of the .BoundColumn
- Several methods for returning data are provided below:
VBA Userform Code Snippet:
With Me.ListBox1 'Set .BoundColumn property. Determines source of listbox .Value property. .BoundColumn = 1 'Return value or content from bound column of selected row. MsgBox .Value 'Set .TextColumn property. Determines source of listbox .Text property. .TextColumn = 3 'Note: .BoundColumn and .TextColumn properties settings are indexed starting at 1 _ .BoundColumn = 1 actually means listbox column 0. MsgBox .Text 'Returns value in the second column of the selected row [.ListIndex] _ Remember listbox columns are indexed starting with 0. MsgBox .List[.ListIndex, 1] 'Returns data contained in the third column of the row selected. MsgBox .Column[2] 'Returns specifically column 0, ListIndex 0 [first item/first column] MsgBox .Column[0, 0] End With
Example 2 - There may be times when you want multi-column data available, but you only want to display primary data and then use all or only parts of the available data. In this case you collect the data from the source as previously shown and then hide all but the primary data.
For this example, I used a source document saved as "D:\Data Stores\sourceWordII.doc"
The results
The code for populating the listBox shown above and for displaying the result in the document is provided and explained below:
VBA Script:
Option Explicit Private Sub Userform_Initialize[] Dim sourcedoc As Document Dim i As Long, j As Long, m As Long, n As Long Dim strColWidths As String 'Define an array to be loaded with the data Dim arrData[] As String Application.ScreenUpdating = False 'Open the file containing the table with items to load Set sourcedoc = Documents.Open[FileName:="D:\Data Stores\sourceWordII.doc", Visible:=False] 'Get the number members = number of rows in the table of details less one for the header row i = sourcedoc.Tables[1].Rows.Count - 1 'Get the number of columns in the table of details j = sourcedoc.Tables[1].Columns.Count 'Set the number of columns in the Listbox to match the number of columns in the table of details ListBox1.ColumnCount = j 'Dimension arrData ReDim arrData[i - 1, j - 1] 'Load table data into arrData For n = 0 To j - 1 For m = 0 To i - 1 arrData[m, n] = Main.fcnCellText[sourcedoc.Tables[1].Cell[m + 2, n + 1]] Next m Next n 'Build ColumnWidths statement strColWidths = "50" For n = 2 To j strColWidths = strColWidths + ";0" Next n 'Load data into ListBox1 With ListBox1 .List[] = arrData 'Apply ColumnWidths statement .ColumnWidths = strColWidths End With 'Close the file containing the individual details sourcedoc.Close SaveChanges:=wdDoNotSaveChanges End Sub Private Sub CommandButton1_Click[] 'Write column data to named bookmarks in document With ActiveDocument 'Note calls to external procedures Main.FillBMs .Bookmarks["Name"], Me.ListBox1.Column[0] Main.FillBMs .Bookmarks["Email"], Me.ListBox1.Column[1] Main.FillBMs .Bookmarks["PhoneNumber"], Me.ListBox1.Column[2] End With Me.Hide lbl_Exit: Exit Sub End Sub
You may have noticed a few differences between this example and the previous example.
- Only column 0 data is displayed to the user in the userform.
- The number of columns is dynamic and determined by the data source.
- Calls to external procedures are used in the userform Initialize and CommandButton_Click events.
The code pane below depicts a standard code module containing the code used to initiate and call the userform shown above and code to process the user selection in the form.
VBA Standard Code Module Script:
Option Explicit Sub CallUF[] Dim oFrm As frmData Set oFrm = New frmData oFrm.Show Unload oFrm Set oFrm = Nothing lbl_Exit: Exit Sub End Sub Sub FillBMs[ByRef oBMPassed As Bookmark, strTextPassed As String] 'Bookmark and ListBox column data passed as parameters Dim oRng As Word.Range Dim strName As String Set oRng = oBMPassed.Range strName = oBMPassed.Name 'Get bookmark name oRng.Text = strTextPassed 'Write ListBox column data to bookmark range [Note: This destroys the bookmark] ActiveDocument.Bookmarks.Add strName, oRng 'Recreate the bookmark spanning the range text lbl_Exit: Exit Sub End Sub Function fcnCellText[ByRef oCell As Word.Cell] As String 'Strip end of cell marker. fcnCellText = Left[oCell.Range.Text, Len[oCell.Range.Text] - 2] lbl_Exit: Exit Function End Function
Access Database
For the two examples in used Access database files as the external data source.
Example 1 used a file named D:\Data Stores\sourceAccess.mdb. The database contains fields for the following information:
- Employee Name
- Employee DOB
- Employee ID
2. I am not an Access guru and much of what you see here is simple a result of "monkey see, monkey do." I can usually work out a basic database table and code, but anything beyond that is over my head. If any Access gurus visit this page I would certainly appreciate any suggestions you might make to improve the content!!
The code for populating the listbox shown above and for displaying the result in the document is provided and explained below:
VBA Userform Script:
Option Explicit 'Requires a reference to the '"Microsoft DAO 3.51 [or 3.6] Object Library." Private Sub Userform_Initialize[] Dim myDataBase As DAO.Database Dim myActiveRecord As DAO.Recordset Dim i As Long 'Open the database to retrieve data Set myDataBase = OpenDatabase["D:\Data Stores\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. Do While Not myActiveRecord.EOF 'Use .AddItem method to add a new row for each record 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 lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click[] Dim oRng As Word.Range Dim oBM As Bookmarks Set oBM = ActiveDocument.Bookmarks Set oRng = oBM["EmpName"].Range oRng.Text = ListBox1.Text oBM.Add "EmpName", oRng Set oRng = oBM["EmpDOB"].Range oRng.Text = ListBox1.List[ListBox1.ListIndex, 1] oBM.Add "EmpDOB", oRng Set oRng = oBM["EmpID"].Range oRng.Text = ListBox1.List[ListBox1.ListIndex, 2] oBM.Add "EmpID", oRng Me.Hide lbl_Exit: Exit Sub End Sub
Example 2 uses the same data but in a Access 2007/2010 .adddb format database file and a slight variation in method:
VBA UserForm Script:
Private Sub Userform_Initialize[] 'You need remove the reference to the '"Microsoft DAO 3.51 [or 3.6] Object Library." _ and add a reference to the Microsoft Office 14 [or 12] Access database engine Object Library." Dim db As DAO.Database Dim rs As DAO.Recordset Dim NoOfRecords As Long 'Open the .accdb form database to retrieve data Set db = OpenDatabase["D:\Data Stores\sourceAccess.accdb"] '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 lbl_Exit: Exit Sub End Sub
| DAO 3.6 | Office 14.0 [or 12.0] |
| You have to remove the reference to the DAO 3.6 before you can reference the Office 14.0 Access database engine Object. |
Excel Spreadsheet
The next three examples use the same userform. For the first example, I used a spreadsheet file "D:\Data Stores\sourceSpreadsheet.xls." The method uses a technique called "Late Binding" where no reference to the Excel Object Library is required:
The spreadsheet data source
VBA Userform Script:
Private Sub Userform_Initialize[] 'Late binding. No reference to Excel Object required. 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["D:\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. With Me.ListBox1 For i = 2 To cRows 'Use .AddItem property to add a new row for each record and populate column 0 .AddItem xlWS.Range["mySSRange"].Cells[i, 1] 'Use .List method to populate the remaining columns .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 lbl_Exit: Exit Sub End Sub
Example 2 uses a method called "Early Binding." It uses an array variable and the Excel .RefersToRange property to retrieve the spreadsheet data.
VBA Userform Script:
Private Sub Userform_Initialize[] 'Uses early binding and requires a reference to the Excel Object Library Dim xlApp As Excel.Application Dim xlbook As Excel.Workbook Dim Listarray As Variant Dim bStartApp As Boolean On Error Resume Next Set xlApp = GetObject[, "Excel.Application"] If Err Then bStartApp = True Set xlApp = New Excel.Application End If On Error GoTo 0 With xlApp Set xlbook = .Workbooks.Open["D:\Data Stores\sourceSpreadsheet.xls"] Listarray = xlbook.Names["mySSRange"].RefersToRange.Value xlbook.Close SaveChanges:=False Set xlbook = Nothing End With If bStartApp Then xlApp.Quit Set xlApp = Nothing With ListBox1 .ColumnCount = UBound[Listarray, 2] .Clear .List[] = Listarray End With lbl_Exit: Exit Sub End Sub
The third Excel method uses the DAO object [similar to the Access method] to retrieve Excel data:
VBA Script:
Private Sub Userform_Initialize[] 'Use DAO object. Requires reference to DAO 3.51 [3.6] Object Libray or Micorsoft Office _ 14.0 [12.0] Access database enginge Object Library" Dim strOffice As String Dim i As Long Dim db As DAO.Database Dim rs As DAO.Recordset strOffice = "mySSRange" 'Open the spreadsheet containing the data Set db = OpenDatabase["D:\Data Stores\sourceSpreadsheet.xls", False, False, "Excel 8.0; IMEX=1;"] 'Use the following code line for Excel 2007/2010 .xlsx format file. 'Set db = OpenDatabase["D:\Data Stores\sourceSpreadSheet.xlsx", False, False, "Excel 12.0; IMEX=1;" '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 listbox with the retrieved records ListBox1.Column = rs.GetRows[i] 'Cleanup rs.Close db.Close Set rs = Nothing Set db = Nothing lbl_Exit: Exit Sub End Sub
1. See the MVP FAQ: Load a Listbox from a Name Range in Excel using DAO for a detailed discussion of the above method and its advantages.
2. See my addendum tips page: Populate Userform Listbox From XML Source for a method of retrieving data from an XML source file.
Cascading Listboxes
In the following examples I demonstrate a few methods for populating secondary and tertiary listboxes based on the value chosen in a parent primary listbox. In the listbox depicted below, the list members displayed in the secondary "Category" listbox determined by the user selection in the primary "Manufacture" listbox. The list displayed in tertiary "Model" listbox is determined by the user selection in the "Category" listbox.
The data source for the userform listboxes shown above is contained in an external Word document table as show in the illustration below.
- Non-printing characters are displayed so you can see that the listbox1 list members are taken from a unique single cell as seen on the left.
- The listbox2 list members are a taken from unique single paragraph in a cell associated with the manufacturer cell [i.e., in the same row].
- The listbox3 list members are taken from a grouping of members separated by the pipe "|" character contained in an individual unique paragraph associated with the paragraph of the category members [i.e., the Desktop category and Desktop models are both listed in paragraph 1 or their respective cells].
The code to initial and display the form is provided in Demo Pack "UserFormJ" which you can download using the link at the end of this page.
The initial form displays only a manufacture:
Initial userform display
When the user selects a PC manufacture a ListBox1_Change event procedure is used to populate the "Category" listbox2:
Display following user selection "Dell"
When the user selects a category we use the ListBox2_Change event to process the code that populates ListBox3
Display following user selection "Notebook"
The following provides and example of code 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 indefinitely. Here is an "abbreviated" example [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
Wrapping it up, I will close with a few examples for demonstrating multi-select listboxes. A lot of people get tripped up using the Listbox.Selected property. Hopefully this will help.
A listbox can be configured to allow single or multiple item selection. In the following example the user is asked to choose their two favorite sports from a list.
Code in the ListBox_Change event and the Command Button_Click event ensure that two and only two selections are made and processes the results:
VBA Userform Script:
Option Explicit Private Sub ListBox1_Change[] Dim i As Long Dim lngCount As Long lngCount = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected[i] Then lngCount = lngCount + 1 End If Next i 'Don't let user select more than two list members. If lngCount > 2 Then ListBox1.Selected[ListBox1.ListIndex] = False MsgBox "Two items are already selected. Please de-select an item to continue." Exit Sub End If lbl_Exit: Exit Sub End Sub Private Sub CommandButton1_Click[] Dim i As Long Dim lngCount As Long Dim strPicks As String lngCount = 0 'Make sure user selects two list members. For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected[i] Then lngCount = lngCount + 1 If lngCount = 1 Then strPicks = ListBox1.List[i] Else strPicks = strPicks & " and " & ListBox1.List[i] End If End If Next i If lngCount = 2 Then MsgBox strPicks Me.Hide Else MsgBox "Please select two items from the list." End If lbl_Exit: Exit Sub End Sub
That's it! I hope you have found this tips page useful and informative. You can download the demonstration files I used to create this tips page here: Populate Userform Listbox Demo Pack
UPDATE: Over the years the demonstration document used to produce this tips page had grown unwieldy. If an effort to present the material in a more organized manner without having to completely revise this page, I've added a new version as part of my more recent Populate UserForm ListBox or ComboBox w\Advanced Functions tips page. Please visit that page to download the new version and a whole lot more!