Create Multi-Select List Box in UserForm with Excel VBA

Creating a multi-select ListBox in an Excel UserForm using VBA can be quite useful when you want to allow users to select multiple items from a list. Below is a detailed explanation and the corresponding VBA code to create a UserForm with a multi-select ListBox.

Step-by-Step Guide:

1. Create a New UserForm:
   • Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
   • In the VBA editor, right-click on VBAProject (YourWorkbookName) in the Project Explorer, choose Insert, then select UserForm.
   • This will create a new UserForm. You can rename it to something meaningful (e.g., MultiSelectForm).
2. Add Controls:
   • ListBox: From the Toolbox, add a ListBox to the UserForm.
   • CommandButton: Add a CommandButton (this will be used to confirm the selections).
   • Optional: You can also add a Label to instruct the user or any other control as needed.
3. Configure the ListBox:
   • Set the MultiSelect property of the ListBox to 1 – fmMultiSelectMulti so that users can select multiple items.
   • You can add items to the ListBox either via VBA code or manually through the RowSource property, but using VBA gives you more flexibility.
4. VBA Code: Now, let’s add the code to populate the ListBox with items and capture the selections.

Example Code:

Private Sub UserForm_Initialize()
    ' Populate the ListBox with sample data
    With ListBox1
        .AddItem "Apple"
        .AddItem "Banana"
        .AddItem "Orange"
        .AddItem "Grapes"
        .AddItem "Pineapple"
        .AddItem "Strawberry"
    End With
End Sub

Private Sub CommandButton1_Click()
    Dim selectedItems As String
    Dim i As Integer
    ' Loop through the ListBox to capture the selected items
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            ' Append the selected item to the string
            selectedItems = selectedItems & ListBox1.List(i) & vbCrLf
        End If
    Next i   
    ' Display the selected items (for example, in a message box)
    If Len(selectedItems) > 0 Then
        MsgBox "You selected: " & vbCrLf & selectedItems, vbInformation, "Selections"
    Else
        MsgBox "No items selected!", vbExclamation, "No Selection"
    End If
End Sub

Explanation:

• UserForm_Initialize:
  • This is the event that runs when the UserForm is loaded. It populates the ListBox with sample items (like fruits in this case).
  • The AddItem method adds each item to the ListBox. You can customize this list with any data you need.
• CommandButton1_Click:
  • This is the event handler for the CommandButton click.
  • The code loops through each item in the ListBox using ListCount (the total number of items in the ListBox).
  • ListBox1.Selected(i) checks whether the item at index i is selected. If selected, the item is added to the selectedItems string, followed by a new line (vbCrLf).
  • After looping through all items, it checks if any items were selected. If yes, it shows a message box with the selected items. If no items are selected, it alerts the user with a message saying « No items selected ».

Enhancements:

• Populate ListBox Dynamically: You can populate the ListBox from a range in a worksheet, for example:

Private Sub UserForm_Initialize()

    Dim ws As Worksheet

    Dim rng As Range

    Dim cell As Range

    Set ws = ThisWorkbook.Sheets(« Sheet1 »)

    Set rng = ws.Range(« A1:A10 ») ‘ Adjust range accordingly

    For Each cell In rng

        ListBox1.AddItem cell.Value

    Next cell

End Sub

• Return Selected Items to a Worksheet: Instead of just showing the selections in a message box, you can write the selected items to a worksheet:

Private Sub CommandButton1_Click()

    Dim selectedItems As String

    Dim i As Integer

    Dim row As Integer

    row = 1 ‘ Starting row for output

    ‘ Loop through the ListBox to capture the selected items

    For i = 0 To ListBox1.ListCount – 1

        If ListBox1.Selected(i) Then

            ‘ Write the selected item to the worksheet

            ThisWorkbook.Sheets(« Sheet1 »).Cells(row, 1).Value = ListBox1.List(i)

            row = row + 1

        End If

    Next i

End Sub

This will output the selected items to Sheet1, starting from cell A1.

Summary:

• A multi-select ListBox in a UserForm allows users to select multiple items from a list.
• You can populate the ListBox dynamically and capture the selected items using VBA code.
• The MultiSelect property is crucial for allowing multiple selections.
• You can customize the actions triggered by the selections, like displaying them in a message box or writing them to a worksheet.