Listboxes on userforms with Excel VBA macros

Listboxes are often used as controls on Userforms. They show a list of items or values, and then the user can pick one or more. What the user does, determines what to do next.

This page shows examples on how to fill and handle listboxes on your own userforms. It also shows how to preselect items on the list. To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into the userforms code.

The procedures are pretty much the same for ComboBoxes, so I'll skip comboboxes or cover them some other time.

If you want to play along as we proceed, now is a good time to open Excel and the VBA editor (ALT+F11) and insert a new Userform. Add a listbox and a command button. Click on the userform and press F7 to open the code window. Then you are ready.

How to fill a ListBox

A listbox can be filled by using cells in the worksheet as source - the list's Rowsource - or by adding items one by one with the AddItem method.

If the items are dates, it can be tricky to display them in the desired format - read more about this on Date format in a ComboBox or ListBox.

RowSource

One of a listbox' properties is "RowSource", which is the address of a range in the spreadsheet, e.g. "Sheet1!A1:A15".

The list's RowSource can be written directly in the listbox' property window (press F4 if it isnt visible), or you can define the range in the userform's Initialize procedure.

It is important to know that RowSource must be of the data type String (text that is), and that it points to the active sheet if nothing else is specified.

If you define RowSource in the userform's Initialize procedure, it could look like this:

Private Sub UserForm_Initialize()
   ListBox1.RowSource = "Sheet1!A1:A15"
End Sub

You can also use a named range. If for instance the range is named "spring" it will look like this:

Private Sub UserForm_Initialize()
   ListBox1.RowSource = Range("spring").Address
End Sub

Dynamic range as RowSource

The procedure above works fine, if it is always the same range you use as rowsource, but what if it changes from time to time?

Then you must use a dynamic range as rowsource in the Initialize procedure. In the following example we find the number of rows down to the first empty cell and use it as rowsource.

Private Sub UserForm_Initialize()
Dim rRange As Range

On Error GoTo ErrorHandle

'We set our range = the cell A1 in Sheet1
Set rRange = Worksheets("Sheet1").Range("A1")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'The range's address is our rowsource
ListBox1.RowSource = rRange.Address

BeforeExit:
Set rRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Fill the list with AddItem

Instead of defining a rowsource in the spreadsheet you can add the items one by one with the AddItem method. You can do it manually or with a loop, where you read from a range, a collection or the like.

Private Sub UserForm_Initialize()

On Error GoTo ErrorHandle

With ListBox1
   .AddItem "Line 1"
   .AddItem "Line 2"
   .AddItem "Line 3"
   .AddItem "Line 4"
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

Private Sub UserForm_Initialize()
Dim lCount As Long

On Error GoTo ErrorHandle

For lCount = 1 To 4
   ListBox1.AddItem "Line " & lCount
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

You can also loop through a range and use the AddItem method. It is slower than using the range's address as rowsource, but it gives you more freedom to be picky.

In the following example we loop through a dynamic range in column A. Imagine that the cells contain a mix of text, dates and numbers, and you want the dates only.

Private Sub UserForm_Initialize()
Dim rRange As Range
Dim rCell as range

On Error GoTo ErrorHandle

'We set our range = cell A1 in Sheet1
Set rRange = Worksheets("Sheet1").Range("A1")

'Check if the cell is empty
If Len(rRange.Formula) = 0 Then
   MsgBox "The list is empty"
   GoTo BeforeExit
End If

'Finds the next empty row and expands rRange
If Len(rRange.Offset(1, 0).Formula) > 0 Then
   Set rRange = Range(rRange, rRange.End(xlDown))
End If

'Now we loop through the range, and only dates are added to the list.
For Each rCell In rRange
   If IsDate(rCell.Value) Then
      ListBox1.AddItem rCell.Value
   End If
Next

BeforeExit:
Set rRange = Nothing
Set rCell = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Using an array as listbox source

When you use the AddItem method, you can also use an array as source, however that is unnecessary, because you can set the list = an array in one operation - not unlike the RowSource method.

The following procedure fills an array with the numbers 1 to 100 and then populates the listbox - it is quite simple.

Sub ArrayToListBox()
Dim lCount As Long
Dim myArray(1 To 100, 1 To 1) As Long

'Fill the array
For lCount = 1 To 100
   myArray(lCount, 1) = lCount
Next

'Set the list = the array
With UserForm1
   .ListBox1.List = myArray
   .Show
End With

End Sub

The list can have more than one column. In the following example we fill an array with 2 columns and use it as source for a listbox list.

Sub Array2ToListBox()
Dim lCol1 As Long
Dim lCol2 As Long
Dim myArray(1 To 100, 1 To 2) As Long

'Fill the array
For lCol1 = 1 To 100
   lCol2 = lCol2 + 4
   myArray(lCol1, 1) = lCol1
   myArray(lCol1, 2) = lCol2
Next

With UserForm1
   .ListBox1.ColumnCount = 2  'Set the list to 2 columns
   .ListBox1.List = myArray   'Set the list = the array
   .Show                      'Show the userform
End With

End Sub

A ListBox with unique items

If you want a list without duplicates (and maybe even in alphabetic order) with a range as source, you start by making a collection as described near the bottom of the page How to make your own collections in VBA Excel (advanced collection).

Imagine that we have named our collection "colList". Then we make our list like below. To make the code work you must declare colList as a collection and write the procedure (here: "MakeCollection") that makes our collection.

Private Sub UserForm_Initialize()
Dim lCount As Long

'Calls the procedure that makes our collection
MakeCollection

'Fill the list
With colList
   For lCount = 1 To .Count
      ListBox1.AddItem .Item(lCount)
   Next
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

How to preselect items in a Listbox

You may want to preselect items in a listbox. There can be many reasons, but one could be to indicate, which items are already in use and that they can be deselected.

You can do that by looping through the list and set the items' Selected property = True. In the following example we insert the numbers from 1 to 10, and if the number divided by 2 leaves 0 (zero), we preselect it.

Private Sub UserForm_Initialize()
'The form's Initialize procedure
'executes before the form opens.
Dim lCount As Long

'To save time we use the With...End With construction,
'when we work on an object several times.
With ListBox1
   'Enables multiple selections
   .MultiSelect = fmMultiSelectMulti
   
   'Selects the list style with small boxes.
   .ListStyle = fmListStyleOption
   
   'With the counter lCount we add the numbers
   'from 1 to 10.
   For lCount = 1 To 10
      .AddItem lCount
   Next
   
   'Now we loop through the list and divide every value
   'by 2. If it leaves zero, we select the item.
   'Being an array the list's first item has index 0,
   'so we loop from 0 to number of items minus 1.
   '".List(lCount)" returns an item's value, which
   'could also be text.
   For lCount = 0 To .ListCount - 1
      If .List(lCount) Mod 2 = 0 Then
         .Selected(lCount) = True
      End If
   Next
End With

End Sub

If you have an array or a range with True/False or numeric values, you can also set the item's Selected property = the value in your array/range. "True" will preselect the item, and so will any number different from zero.

It could look like this, where rRange is a range with True/False or numbers in the cells:

   For lCount = 0 To .ListCount - 1
      .Selected(lCount) = rRange.Item(lCount + 1).Value
   Next

When the user has selected from the list

When the user has selected from the list, we must find out what he selected. Here it is decisive whether the listbox is set up for one choice or multiple. That is defined in the ListBox properties window in "MultiSelect", but the property can also be set at runtime with:

ListBox1.MultiSelect = fmxxxx (3 choices)

If the user can select only one thing, it is easy. Then you find the selection with:

If the user can select multiple items, you cannot use ListBox1.Value. Instead you need the ListBox' Selected property.

Here is an example with a command button, where we loop through the list, and the selected items are inserted in cell B1 and down.

For lCount = 0 To .ListCount - 1

That is because the first item isn't number 1, but "0" - zero like in arrays. If for instance you have 10 items on your list, "ListBox1.Count" will return the number "10", but if you loop the list from zero, you will be at item nb. 10 when you reach 9.

Private Sub CommandButton1_Click()
Dim rRange As Range
Dim lCount As Long   'Counter

On Error GoTo ErrorHandle

'Cell B1 is selected for input
Set rRange = Range("B1")

'We loop through the list, and selected items
'are inserted into B1 and downwards.
With ListBox1
   For lCount = 0 To .ListCount - 1
      If .Selected(lCount) = True Then
         rRange.Offset(lCount, 0).Value = .List(lCount)
      End If
   Next
End With

BeforeExit:
Set rRange = Nothing
Unload Me

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub