Excel vba listbox selection

The VBA ListBox is a very useful control. If you are creating any kind of UserForm application you will most likely use it.

In this post, I’m going to show you everything you need to know about the VBA ListBox so you can avoid the common pitfalls and get up and running quickly and easily.

What is the VBA ListBox used for?

The ListBox is used to display a list of items to the user so that the user can then select one or more. The ListBox can have multiple columns and so it is useful for tasks like displaying records.

VBA ListBox versus the VBA ComboBox

The ListBox is very similar to the ComboBox which also allows the user to select an item from a list of items. The main differences are:

1. The Listbox allows multiple selections. The Combobox only allows one selection.
2. Items in the ListBox are always visible. The Combobox items are only visible when you click on the “down” icon.
3. The ComboBox has the ability to filter the contents when you type.

The VBA ListBox Properties Quick Guide

How to Add Items to the ListBox

There are 3 ways to add items to the VBA Listbox:

1. One at a time using the AddItem property.
2. Adding an array/range using the List property.
3. Adding a Range using the RowSource property.

The List and RowSource properties are the most commonly used. The table below provides a quick comparison of these properties:

VBA ListBox List Property

The List property allows you to add to contents of an array to a ListBox. As Range.Value is an array you can copy the contents of any range to the Listbox.

Here are some examples of using the List property:

' Add the contents of an array
ListBox1.List = Array("Apple", "Orange", "Banana")

' Add the contents of a Range
ListBox1.List = Range("A1:E5").Value

You can also use the List property to write from the ListBox to an array or range:

Range("A1:B3").Value = ListBox1.List

Important Note: If there is only one item in a range then VBA doesn’t covert it to an array. Instead, it converts the range to a string/double/date etc.

Sheet1.Range("A1:A2").Value ' Array
Sheet1.Range("A1").Value ' Single value variable

In this case, you need to use AddItem to add the value to the ListBox:

 If myRange.Count = 1 Then
    ListBox1.AddItem myRange
 Else
    ListBox1.List = myRange.Value
 End If

The List Property and Column Headers

The ListBox only displays column headers if you use RowSource. Otherwise, they are not available. The best way to add column headers(and it’s not a great way) is to add Labels above the ListBox columns. One advantage is that you can use the click event of the Label if you want to implement something like sorting.

Updating Items using the List Property

You can update individual items in the ListBox using the List Property.

Imagine we have a ListBox with data like this:

If we want to change Nelson in row 3, column 2 we do it like this:

ListBox1.List(2, 1) = "SMITH"

The result we get is:

The List property rows and columns are zero-based so this means row 1 is 0, row 2 is 1, row 3 is 2 and so on:

VBA ListBox RowSource

The RowSource property allows us to add a range to the ListBox. This is different from the List Property in that the Range is linked to the ListBox. If data in the Range changes then the data in the ListBox will update automatically.

When we use RowSource the data in the ListBox is read-only. We can change the RowSource range but we cannot change the values in the ListBox.

How to use RowSource

We add the RowSource range as a string like this:

 ListBox1.RowSource = "Sheet1!A1:A5"

If you don’t specify the sheet the VBA will use the active sheet

 ListBox1.RowSource = "A1:A5"

If you are using the Address of a range object with RowSource then it is important to use the External parameter. This will ensure that RowSource will read from the  sheet of the range rather than the active sheet:

 ' Get the range
 Dim rg As Range
 Set rg = Sheet1.Range("A1:A5")

 ' Address will be $A$1:$A$5 which will use the active sheet
 ListBox1.RowSource = rg.Address
 Debug.Print ListBox1.RowSource

 ' Address will be [Book2]Sheet1!$A$1:$A$5 which will use Sheet1
 ListBox1.RowSource = rg.Address(External:=True)
 Debug.Print ListBox1.RowSource

RowSource Column Headers

Column headers are automatically added to the ListBox when you use the RowSource property. The ColumnHeads property must be set to True or the headers will not appear. You can set this property in the code or in the properties window of the ListBox.

  ListBox1.ColumnHeads = True

The column headers are taken from the row above the range used for the RowSource.  For example, if your range is A2 to C5 then the column header will use the range A1 to C1:

Here is an example: We want to add the data below to our ListBox and we want A1 to C1 to be the header.

We set the RowSource property to A2:C5 and set the ColumnHeads property to true:

With ListBox1
    .RowSource = "sheet1!A2:C5"
    .ColumnHeads = True
    .ColumnWidths = "80;80;80"
End With

The result will look like this:

VBA ListBox AddItem

It is very rare that you would use the AddItem property to fill the ListBox. List and RowSource are much more efficient. AddItem is normally used when the Listbox already has items and you want to add a new item.

The AddItem property is simple to use. You provide the item you want to add as a parameter. The ListBox will automatically add it as the last item:

With ListBox
    .AddItem "Apple"
    .AddItem "Orange"
End With

If you want to Insert the item at a certain position you can use the second parameter. Keep in mind that this is a zero-based position, so if you want the item in position one then the value is 0, position 2 the value is 1, and so on.

With ListBox1
    .AddItem "Apple"
    .AddItem "Orange"
    
    ' Add "Banana" to position 1(Index 0)
    .AddItem "Banana", 0
End With

The order will be:
Banana
Apple
Orange

If you want to add multiple columns with AddItem then you need to use the List property after you use AddItem:

 With listboxFruit
    .List = myRange.Value
    .AddItem "Banana"
    
    ' Add to the second column of 'Banana' row
    .List(2, 1) = "$2.99"
 End With

One reason for using AddItem  is if you are adding from data that isn’t sequential so you cannot use the List or RowSource properties:

 Dim cell As Range
 ' Fill items with first letter is A
 For Each cell In Sheet1.Range("A1:A50")
    If Left(cell.Value, 1) = "A" Then
        comboBoxFruit.AddItem cell.Value
    End If
 Next

Important Note: If you fill a ListBox with RowSource then you cannot use AddItem to add a new item. If you try you will get a “Runtime Error 70 – Permission Denied”.

VBA ListBox Selected Items

If only one item is selected then you can use ListIndex to get the selected row. Remember that it is zero-based so row 1 in the ListBox is at ListIndex 0, row 2 at ListIndex 1 and so on.

   MsgBox "The selected item is " & ListBox1.ListIndex

If the ListBox has multiple columns then you can use the ListIndex and List properties together to return a value in the selected row:

  ' Display the value from the second column of the selected row
  MsgBox ListBox1.List(ListBox1.ListIndex, 2)

If multiple items are selected then you can use the GetSelectedRows function which returns a collection of selected rows:

 Sub Example()
    
    ' Store the row numbers of selected items to a collection
    Dim selectedRows As Collection
    Set selectedRows = GetSelectedRows()
    
    ' Print the selected rows numbers to the Immediate Window
    Dim row As Long
    For Each row In selectedRows
        ' Print to the Immediate Window Ctrl + G
        Debug.Print row
    Next row

 End Sub

 ' Returns a collection of all the selected items
 Function GetSelectedRows() As Collection

    ' Create the collection
    Dim coll As New Collection

    ' Read through each item in the listbox
    Dim i As Long
    For i = 0 To listboxFruit.ListCount - 1
    
        ' Check if item at position i is selected
        If listboxFruit.Selected(i) Then
            coll.Add i
        End If
    Next i

    Set GetSelectedRows = coll

End Function

Reading Data from the VBA Listbox

To read data from the ListBox we can use the ListBox.Value property. This only works when the ListBox is set to only select one item i.e. MultiSelect is set to frmMultiSelectSingle(see the section VBA ListBox MultiSelect below for more about this).

Single selection only  with one column

When only one item is selected we can use the Value property to get the currently selected item:

 Dim fruit As String
 fruit = ListBox1.Value

Keep in mind that if there are multiple columns, Value will only return the value in the first column.

Single selection only with multiple columns

If the ListBox has Multiple columns you can use the Value property to get the value in the first column. You need to read through the List property to get the values in the other column(s). The List property is essentially an array so you can treat it like one.

In the example below we read through the columns of row 1(the index of row 1 is 0):

 With ListBox1
 
     For j = LBound(.List, 2) To UBound(.List, 2)
         ' Print the columns of the first row to the Immediate Window
         Debug.Print .List(0, j)
     Next j
     
 End With

Normally you want to print the values in the selected row. You can use the ListIndex property to get the selected item(Note that ListIndex returns the last selected items so it won’t work where there are multiple items selected):

 ' ExcelMacroMastery.com
 Sub ReadValuesFromSelectedRow()

    ' Write contents of the row to the Immediate Window(Ctrl G)
    With ListBox1 
        For j = LBound(.List, 2) To UBound(.List, 2) 
            ' Print the columns of the selected row to the Immediate Window 
            Debug.Print .List(.ListIndex, j) Next j 
    End With
 End Sub
 

Multiple selections

If the ListBox has multiple selections and you want to get all the data from each then you can use the GetSelectedRows() sub from the section VBA ListBox Selected Items. This will get a collection of all selected rows. You can use this to print the data from the selected rows:

Sub PrintMultiSelectedRows()

    ' Get all the selected rows
    Dim selectedRows As Collection
    Set selectedRows = GetSelectedRows(Me.ListBox1)

    Dim i As Long, j As Long, currentRow As Long
    ' Read through the selected rows
    For i = 1 To selectedRows.Count
        With ListBox1
            
            ' Get the current row
            currentRow = selectedRows(i)
            
            ' Print row header
            Debug.Print vbNewLine & "Row : " & currentRow
            
            ' Read items in the current row
            For j = LBound(.List, 2) To UBound(ListBox1.List, 2)
                ' Print the columns of the first row to the Immediate Window
                Debug.Print .List(currentRow, j)
            Next j
        
        End With
    Next i
    
End Sub

Function GetSelectedRows(currentListbox As MSForms.ListBox) As Collection

    ' Create the collection
    Dim coll As New Collection

    ' Read through each item in the listbox
    Dim i As Long
    For i = 0 To currentListbox.ListCount - 1
    
        ' Check if item at position i is selected
        If currentListbox.Selected(i) Then
            coll.Add i
        End If
    Next i

    Set GetSelectedRows = coll

End Function

VBA ListBox MultiSelect

We can use the MultiSelect property of the ListBox to allow the user to select either a single item or multiple items:

There are 3 selections:

• 0 = frmMultiSelectSingle –  [Default]Multiple selection isn’t allowed.
• 1 = frmMultiSelectMulti – Multiple items are selected or deselected by choosing them with the mouse or by pressing the Spacebar.
• 2 = frmMultiSelectExtended – Multiple items are selected by holding down Shift and choosing them with the mouse, or by holding down Shift and pressing an arrow key to extend the selection from the previously selected item to the current item. You can also select items by dragging with the mouse. Holding down Ctrl and choosing an item selects or deselects that item.

VBA ListBox Columns

You can have multiple columns in a ListBox. For example, you can load a Range or two-dimensional array to a ListBox using List or RowSource.

Often when you load data with multiple columns only one column appears. This can be very confusing when you are using the Listbox. To get the columns to appear you have to set the ColumnCount property to the number of Columns.

You should also make sure that the ColumnWidths property is correct or one of the columns may not appear.

You can do it like this:

With listboxFruit
    .RowSource = "Sheet1!A2:B4"
    .ColumnCount = 2
    .ColumnWidths = "100,100"
End With

In a real-world application, you could set the RowSource and ColumnCount properties like this:

With listboxFruit
    .RowSource = myRange.Address(External:=True)
    .ColumnCount = myRange.Columns.Count
End With

See the AddItem section for how to add data to the other columns when you are using the AddItem property.

VBA ListBox Column Headers

Column Headers are another confusing element of the ListBox. If you use the RowSource property to add data to the ListBox then the line above the Range will be automatically used as the header.

For the Column headers to appear the ColumnHeads property must be set to true. You can do this in the properties window of the ListBox or in the code list this:

ListBox1.ColumnHeads = True

If you use the List or AddItem property to fill the ListBox then the column headers are not available. The best solution, albeit a frustrating one, is to use labels above the ListBox. I know it sounds crazy but that unfortunately is the reality. The one advantage is that you can use the Label click event which is useful if you plan to sort the data by a column.

Creating a ListBox Dynamically

Controls are normally created at design time but you can also create them dynamically at run time:

    Dim myListbox As MSForms.ListBox
    Set myListbox = Controls.Add("Forms.ListBox.1")

If you want to add an event to a dynamic control you can do it like this:

1. First of all create a Class like this:

   Public WithEvents myListBox As MSForms.ListBox
   
   Private Sub myListBox_Change()
     MsgBox "Selection changed"
   End Sub
   

2. Name the class clsListBoxEvents.  Create a variable of this class object in the UserForm like this:

   Private listBoxEvents As New clsListBoxEvents
   

3.   Attach the events to the ListBox:

   Sub CreateDynamicListBox()
   
       ' Create the ListBox
       Dim newListBox As MSForms.ListBox
       Set newListBox = Controls.Add("Forms.ListBox.1")
       
       ' Add some items
       newListBox.List = Array("Apple", "Orange", "Pear")
      
       ' Connect the ListBox to the ListBox events class
       Set listBoxEvents.myListBox = newListBox
   
   End Sub
   

Note that you can attach events to any ListBox. It doesn’t have to be created dynamically to do this.

Loop through ListBoxes

If you want to loop through all the ListBoxes on a UserForm you can do it like this:

 Dim ctrl As Variant
 For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ListBox" Then
        Debug.Print ctrl.Name
    End If
 Next ctrl

YouTube Video

Check out this video where I use the ListBox. The source code for the video is available from here

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out this Free VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

This article will demonstrate how to work with the selected item in a List Box in Excel VBA.

List Boxes show a list of options to users, allowing them to select one or more of the items. They are largely used in VBA forms but can also be used within your Excel worksheet.

Create List Box in a VBA Form

To create a list box in a VBA form, we first need to create the UserForm.

Once you have created your form, select the List Box control in the toolbox and then drag to create a list box on your form.

Add Values to the List Box

In the Initialize event of the form, type the following code. The List Box will pick up values that are stored in a Range of Cells in your Excel Worksheet.

Private Sub UserForm_Initialize()
  Dim rng As Range
  For Each rng In Range("A1:A50")
    Me.lstState.AddItem rng.Value
  Next rng
End Sub

When we run the form, the list box will be shown as demonstrated in the image below:

Select Values from the List Box

By default, a single value can be selected in a List Box in a user form.  However this can be amended by changing the Multi-Select property of the list box.

Click on the list box to select it, and then in the Properties window, change the Multi-Select Property from 0-frmMultiSelectSingle to 1-frmMultiSelectMulti.

Now when we run the form, we can select more than one option in the List Box.

If we change the option to be 2-frmMultiSelectExtended, it means that we can select one of the values, and then, holding down the SHIFT key, select another value further down the list, and all the items between the 2 values selected will also be selected.

Work with the Selected Values in VBA

Depending on the type of option we have used for the Multi-Select property in the List Box, there are a number of ways we can use the value or values selected in the list box in VBA Code.

Assigning the Value to a Variable

We can use the After_Update event of the list box to assign the value selected to a variable.

Firstly, let us create a module level variable at the top of the form module.

Underneath the words, Option Explicit, create the following string variable.

Dim strState as String.

Once we have created this variable, we  can double-click on the List box to go to the code behind the form, or we can click on the code button in the VBE Editor.

The Click Event of the list box will be automatically created.  Select the After_Update Event form the list of Procedures available.

In the After_Update event, type the following code:

Private Sub lstState_AfterUpdate()
  strState = Me.lstState
End Sub

NOTE:  You can delete the Click event as it is not required.

Now, if we run the form and click on the list box, the selected value will be store in the variable.  To test this, we can put a BREAK point in the code.

Now when we run the form, if we click on the list box, the code will go into DEBUG mode and stop at our break point.  If we then press F8 on the keyboard to move  a step further in the code, the variable will be populated with the selected item in the list.

We can view this value by resting the mouse on the variable.

OR

We can view the value in the Immediate Window.

Use a Command Button to Return the Value to Excel

Firstly, we create a command button on the form in order to have an OK button to return the value or values selected in the list box to Excel.

Select the Command Button control, and then click and drag in your form to create the button.

In the Properties window, change the name of the button to cmdOK, and change the Caption and Accelerator of the button.

The purpose of the Accelerator is for the user to use the keyboard to activate the button, in this case Alt+O would activate the button.

For the command button to work, we need to add code behind it so that when the button is clicked, the code runs. This is called the Click event of the button.

To get to the click event, double-click on the button in the design view of the form. The click event will be automatically created as this is the event that is most commonly used for Command buttons.

Type the following code into the click event of the command button.

Private Sub cmdOK_Click()
  Range("E1") = strState
End Sub

The code will pick up the variable we declared in the After_Update event of the ListBox and return the value to the Range in Excel.

Alternatively, we can pick up the value directly from the List Box without using a variable.

Private Sub cmdOK_Click()
  Range("E1") = me.lstState
End Sub

When we run the form, the selected value will be returned to Excel when we click the OK button.

Select Multiple Values

If we have set the multi-select property of the list box to 1 or 2 which allows us to select multiple values in the list, then the code to select these values is slightly different.

The After_Update event is no longer fired when selecting the values in the list box – we therefore cannot use this event.

We can still use the command button click event, but we need to loop through the values selected in the list box in order to return them to Excel.

In the command button Click event, type the following code.

Private Sub cmdOK_Click()
  Dim x As Integer
  Range("E1").Select
  For x = 0 To Me.lstState.ListCount - 1
     If Me.lstState.Selected(x) = True Then
        ActiveCell = Me.lstState.List(x)
        ActiveCell.Offset(1, 0).Select
     End If
  Next x
End Sub

Now when we run the form, only the values that are selected will be returned to the Excel sheet.