VBA Listbox – Selected Item

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.