[Excel VBA] Mastering ListBoxes on Sheets! A Thorough Explanation of Major Properties

Introduction

The “Form Control” ListBox, which can be placed directly on an Excel sheet, is a powerful tool for presenting options to users. To manipulate this ListBox freely with VBA, understanding its main properties is essential.

In this article, I will explain the roles and usage of the five most important properties for operating ListBoxes on sheets, complete with sample code.

1. List: Sets the items in the list.
2. ListCount: Gets the total number of items.
3. ListIndex: Gets the number of the selected item.
4. MultiSelect: Sets whether to allow multiple selections.
5. Selected: Determines if each item is selected during multiple selection.

1. List Property

This sets or retrieves the list of items to display in the ListBox. The easiest way to set this is by assigning a VBA array directly.

Sample Code

Sub PopulateListBox()
    Dim targetListBox As ListBox
    Set targetListBox = ActiveSheet.ListBoxes("SalesOfficeList")
    
    ' Assign an array created with the Array function to the .List property
    targetListBox.List = Array("Tokyo HQ", "Osaka Branch", "Nagoya Branch", "Fukuoka Branch")
End Sub

Explanation: Simply assigning an array created with the Array function to the .List property sets the list items all at once.

2. ListCount Property

This retrieves the total number of items contained in the ListBox. It is a read-only property. It is often used when checking all items in a loop.

Sample Code

Sub GetItemCount()
    Dim targetListBox As ListBox
    Set targetListBox = ActiveSheet.ListBoxes("SalesOfficeList")
    
    ' Get the current item count with .ListCount
    MsgBox "Current item count: " & targetListBox.ListCount
End Sub

3. ListIndex Property

This retrieves or sets the index number (starting from 1) of the item currently selected by the user.

• Valid only in single-selection mode.
• Returns 0 if nothing is selected.

Sample Code

Sub SelectAndGetItem()
    Dim targetListBox As ListBox
    Set targetListBox = ActiveSheet.ListBoxes("SalesOfficeList")
    
    ' Select the 2nd item (Osaka Branch)
    targetListBox.ListIndex = 2
    
    ' Get the index number of the currently selected item
    MsgBox "Item number " & targetListBox.ListIndex & " is selected."
End Sub

Note: For UserForm ListBoxes, ListIndex starts at 0, but for ListBoxes on sheets (Form Controls), it starts at 1.

4. MultiSelect Property

Sets whether multiple selections are allowed.

Value	VBA Constant	Description
1	xlNone	Single selection (Default)
2	xlSimple	Multi-select by clicking (no Shift/Ctrl needed)
3	xlExtended	Standard multi-select using Shift/Ctrl keys

Sample Code

Sub EnableMultiSelect()
    Dim targetListBox As ListBox
    Set targetListBox = ActiveSheet.ListBoxes("SalesOfficeList")
    
    ' Enable multi-select using the Ctrl key
    targetListBox.MultiSelect = xlExtended
End Sub

5. Selected Property

When multiple selection is enabled, this determines whether each individual item is selected.

Sample Code

Sub GetSelectedItems()
    Dim targetListBox As ListBox
    Dim i As Long
    Dim resultText As String
    
    Set targetListBox = ActiveSheet.ListBoxes("SalesOfficeList")
    
    ' Exit if multi-select is not enabled
    If targetListBox.MultiSelect = xlNone Then Exit Sub
    
    resultText = "Selected Items:" & vbCrLf
    
    For i = 1 To targetListBox.ListCount
        ' Check if the i-th item is selected using .Selected(i)
        If targetListBox.Selected(i) Then
            resultText = resultText & "- " & targetListBox.List(i) & vbCrLf
        End If
    Next i
    
    MsgBox resultText
End Sub

Explanation: The .Selected property takes an index number (starting from 1) as an argument. It returns True if that item is selected, and False otherwise.

Summary

In this article, I explained five basic properties for manipulating ListBoxes on sheets using VBA.

By combining these properties, you can create interactive sheets where list contents change dynamically or specific processes run based on user selection.