Populate Userform Listbox from Range

In this lesson you will learn how to populate a userform listbox with a range. In VBA (Visual Basic for Applications), a list box is a graphical user interface control that allows users to make a selection from a list of items. It is commonly used in user forms to display data or present a list of options from which the user can choose. When it is used to present a list from which the user can select, the selected item or items in the list box can then be used for further processing in the other VBA codes associated with controls such as command buttons. Or by codes even associated with events such as listbox_click events etc.

A list box can contain a collection of items, typically displayed in a vertical list. These items can be static (defined at design time) or dynamic (populated at runtime). In this example we will use a collection of static items stored in a worksheet. If you want to populate the list box with a dynamic data set, then the best option is to populate data from an array. This post explains how to populate a listbox from an array.

Fill a Listbox From an Array

Name of this worksheet is “EmployeeData”. This worksheet contains Full Name, Job Title, Department, Employee ID and Date of Joining for 10 employees. Now let’s see how we can populate these data into a userform listbox. This is the listbox we are going to use for this example. Name of the form is frmEmployeeData and name of the listbox is lstEmployeeData.

Now we can write the vba code inside the UserForm_Initialize event to populate the listbox with Excel range.

Private Sub UserForm_Initialize()

End Sub

This is how we can populate the list with the above Excel range.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=EmployeeData!A1:E11"
     End With

End Sub

This is how the listbox is populated from the Excel data range.

Remember that you can’t use worksheet names having spaces.In the above example we use an Excel sheet called “EmployeeData”. Assume our worksheet name is “Employee Data”. Then we can’t modify the code as follows.

Private Sub UserForm_Initialize()

     With lstEmployeeData
         .ColumnCount = 5
         .RowSource = "=Employee Data!A1:E11"
     End With

End Sub

Because then it will throw an error like this.