🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA ListBox in Excel — Fill It, Read the Selection, and the MultiSelect Trap

|

VBA ListBox in Excel — Fill It, Read the Selection, and the MultiSelect Trap

TL;DR — A ListBox shows a list and remembers what the user picked. Fill it in one shot with ListBox1.List = Range("A2:A50").Value. Read a single choice with .ListIndex (the 0-based row, or -1 for nothing) and .Value (the chosen text). The trap: the moment .MultiSelect is on, .Value goes Null and stops telling you anything — you must loop .Selected(i) to collect every pick.

' Single-select ListBox: read the one choice
Private Sub cmdOK_Click()
    If ListBox1.ListIndex = -1 Then
        MsgBox "Please pick a name first."
        Exit Sub
    End If
    MsgBox "You picked " & ListBox1.Value      ' .Value = the selected text
End Sub

A ListBox is a control on a UserForm, and the first thing to get straight is the one idea behind every control: a control is a live object you read through its properties, not the value itself. The items live in .List; the user's pick lives in a separate property — .ListIndex or .Selected — never in the box as a whole. Almost every "my ListBox returns nothing" question is reading the wrong property.

What you'll learn

  • The mental model that ties ListBox, ComboBox and CheckBox together
  • Two ways to fill a ListBox — .List = Range.Value in one shot versus .AddItem in a loop
  • How to read a single selection with .ListIndex and .Value, and what -1 means
  • The number-one trap: .Value goes Null under MultiSelect, so you must loop .Selected(i)
  • How to clear a list, show more than one column, and bind it to a sheet with .RowSource
  • When a ListBox is the right control and when a ComboBox fits better

The mental model: the box holds the list, a property holds the pick

Think of a ListBox as a window onto a list of items. Two different things live in two different places:

ListBox1.List           ' the ITEMS - what is shown in the box
ListBox1.ListIndex      ' the PICK  - which row is selected (0-based, -1 = none)
ListBox1.Value          ' the pick's TEXT - only meaningful for single-select
ListBox1.Selected(i)    ' True/False for row i - the only reliable read under MultiSelect

That separation is the whole control. When you set .List, you are loading the items. When the user clicks a row, VBA does not change .List — it changes .ListIndex and flips .Selected. So "what did the user choose?" is never answered by looking at the list; it is answered by reading the selection properties. Get that straight and the rest is detail.

Filling a ListBox: one shot beats a loop

You will see two ways to fill a list. The one to reach for first assigns an entire column at once:

Private Sub UserForm_Initialize()
    ' Fill from a worksheet column in a single assignment - fast and simple
    ListBox1.List = Sheet1.Range("A2:A50").Value
End Sub

.List accepts a 2-D array, and Range.Value is a 2-D array (see VBA Range), so one line loads fifty rows. Use .AddItem only when the items are computed one at a time and you have no array to hand:

Dim i As Long
For i = 1 To 5
    ListBox1.AddItem "Quarter " & i    ' add items you build on the fly
Next i

.AddItem inside a big loop is the slow path — each call is a separate round-trip to the control. If the data already sits in a range or an array, assign .List once. To empty the box before refilling it, call ListBox1.Clear.

Reading a single selection: ListIndex and Value

For an ordinary single-select ListBox, two properties answer everything. .ListIndex is the 0-based row number of the selection — and it is -1 when nothing is selected, which is the guard you should always check first. .Value is the text of the selected row:

If ListBox1.ListIndex = -1 Then
    MsgBox "Nothing selected."          ' -1 is the 'no pick yet' signal
Else
    MsgBox "Row " & ListBox1.ListIndex & ": " & ListBox1.Value
End If

Checking .ListIndex = -1 before you read .Value is the difference between a form that guides the user and one that acts on an empty selection. It is the same discipline as guarding a Find result before you use it.

The number-one trap: MultiSelect makes .Value useless

Here is the mistake that sends people in circles. A ListBox has a MultiSelect property. Leave it at its default (fmMultiSelectSingle) and .Value works. Set it to fmMultiSelectMulti or fmMultiSelectExtended so the user can tick several rows — and .Value goes Null. It no longer reflects the selection at all, because "the value" is meaningless when three rows are chosen.

Under MultiSelect, the only reliable read is to walk every row and test .Selected(i):

Dim i As Long, chosen As String
For i = 0 To ListBox1.ListCount - 1     ' rows are 0 .. ListCount - 1
    If ListBox1.Selected(i) Then
        chosen = chosen & ListBox1.List(i) & vbCrLf
    End If
Next i
MsgBox "You chose:" & vbCrLf & chosen

Two details make or break this loop. It runs from 0 to .ListCount - 1, because rows are 0-based just like array bounds — off-by-one here silently drops the last row. And it reads the item text with .List(i), not .Value, because .Value is Null in this mode. If you built a form, switched on multi-select later, and your code stopped seeing selections, this is why.

More than one column, and binding to a sheet

Two properties cover the common "I need more than a plain list" cases. .ColumnCount and .List (a 2-D array) let a ListBox show several columns — an ID and a name side by side — while .BoundColumn decides which column .Value returns. And .RowSource binds the box directly to a worksheet range so it tracks the cells without any fill code:

ListBox1.ColumnCount = 2
ListBox1.RowSource = "Sheet1!A2:B50"    ' two columns, live-bound to the sheet
ListBox1.BoundColumn = 1                ' .Value returns column 1 (the ID)

.RowSource is convenient but couples the form to a specific sheet layout; assigning .List from an array keeps the form independent of where the data lives. For a data-entry form that outlives one workbook, prefer the array assignment.

ListBox or ComboBox? Pick by how the choice should feel

A ListBox and a ComboBox read almost identically, so choose on behaviour, not syntax. Reach for a ListBox when the options should be visible at a glance or when the user may pick more than one — multi-select is a ListBox-only trick. Reach for a ComboBox when space is tight, the answer is a single value, and a dropdown that collapses to one line is tidier. If you find yourself wanting a checkbox-per-row, you probably want a multi-select ListBox instead.

How ExcelMaster helps

The two mistakes on this page cost real time: reading .Value on a multi-select ListBox (and getting Null), and acting on a selection without first checking .ListIndex = -1. Both are easy to write and quiet to fail.

ExcelMaster lets you describe the form in plain words — "a list of customers the user can multi-select, then write the chosen ones to a sheet" — and it wires up the UserForm, fills the ListBox from your data, and reads the selection the right way (looping .Selected(i) when multi-select is on, guarding .ListIndex when it is not). You keep the workbook and the code.

Frequently asked questions

How do I fill a ListBox from a range in VBA?

Assign the range's value to .List in one statement: ListBox1.List = Sheet1.Range("A2:A50").Value. Because Range.Value is a 2-D array and .List accepts one, this loads the whole column at once. Use .AddItem only for items you build individually in a loop, and call .Clear first if you are refilling a box that already has rows.

How do I get the selected item from a VBA ListBox?

For a single-select ListBox, read .Value for the text or .ListIndex for the 0-based row number; .ListIndex is -1 when nothing is selected, so check that first. For a multi-select ListBox, .Value is Null — loop For i = 0 To ListBox1.ListCount - 1 and collect every row where .Selected(i) is True.

Why is my ListBox .Value empty or Null?

Because MultiSelect is turned on. When .MultiSelect is fmMultiSelectMulti or fmMultiSelectExtended, the .Value property is always Null — it cannot represent several selected rows. Read the selection by looping .Selected(i) across 0 to .ListCount - 1 instead, and read each item with .List(i).

How do I clear all items from a ListBox?

Call ListBox1.Clear. That removes every row. If the box is bound with .RowSource, set .RowSource = "" first, because a bound list rebuilds itself from the range. To clear only the current selection without removing items, set .ListIndex = -1 (single-select) or loop .Selected(i) = False (multi-select).

What is the difference between a ListBox and a ComboBox?

A ListBox shows several rows at once and can allow multiple selections; a ComboBox collapses to a single line, shows a dropdown, and allows only one choice (and optionally free typing). Use a ListBox when the options should be visible or multi-pick, a ComboBox when space is tight and one answer is enough.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-25.

Related guides: VBA ComboBox · VBA CheckBox · VBA UserForm · VBA Range · VBA For Loop