[VBA] How to Add, Remove, and Clear All Items in a ListBox

Introduction

In VBA UserForms, ListBoxes are not just for displaying fixed lists. You can dynamically add items based on user actions, delete selected items, or clear (empty) the entire list. This allows you to create features like shopping carts or task management tools.

In this article, I will explain three basic methods for manipulating ListBox items: .AddItem, .RemoveItem, and .Clear, with concrete sample code.

1. Adding Items (.AddItem)

The .AddItem method adds a new line to the ListBox. In this example, clicking the “Add” button adds the value of the currently selected cell (ActiveCell) to the end of the ListBox.

Sample Code

' Process when the "Add" button (AddItemButton) is clicked
Private Sub AddItemButton_Click()
    ' If ActiveCell value is not empty, add to list
    If ActiveCell.Value <> "" Then
        Me.TaskListBox.AddItem ActiveCell.Value
    End If
End Sub

Explanation of the Code

• Me.TaskListBox.AddItem ActiveCell.Value: This adds ActiveCell.Value as a new item to the ListBox named TaskListBox. The item is added to the end of the list.

2. Removing Selected Items (.RemoveItem)

The .RemoveItem method deletes the row at the specified index number from the ListBox. In this example, clicking the “Remove” button deletes the currently selected item in the ListBox.

Sample Code

' Process when the "Remove" button (RemoveItemButton) is clicked
Private Sub RemoveItemButton_Click()
    With Me.TaskListBox
        ' Get the index of the selected row with .ListIndex and check if an item is selected
        If .ListIndex > -1 Then
            ' Pass the index number to .RemoveItem to delete that row
            .RemoveItem .ListIndex
        Else
            MsgBox "Please select an item to remove.", vbExclamation
        End With
    End With
End Sub

Explanation of the Code

• If .ListIndex > -1 Then: The .ListIndex property returns the index (starting from 0) of the currently selected row. If nothing is selected, it returns -1. This If statement confirms that an item is actually selected.
• .RemoveItem .ListIndex: You pass the index number of the row you want to delete as an argument to the .RemoveItem method. Here, we pass the index of the selected row to delete it.

3. Clearing All Items (.Clear)

The .Clear method deletes all items in the ListBox at once, emptying the list.

Sample Code

' Process when the "Clear All" button (ClearAllButton) is clicked
Private Sub ClearAllButton_Click()
    Me.TaskListBox.Clear
End Sub

Explanation of the Code

• Me.TaskListBox.Clear: This clears all contents of TaskListBox. It requires no arguments and is very simple.

Summary

In this article, I explained three basic methods for dynamically manipulating ListBox items.

• .AddItem: Adds one item.
• .RemoveItem: Deletes one selected item.
• .Clear: Deletes all items.

By mastering these methods, you can create more interactive and practical UserForms where contents change based on user operations.