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

VBA ComboBox in Excel — Fill a Dropdown, Read the Choice, and the ListIndex = -1 Trap

|

VBA ComboBox in Excel — Fill a Dropdown, Read the Choice, and the ListIndex = -1 Trap

TL;DR — A ComboBox is a dropdown: pick one item, or (by default) type your own. Fill it with ComboBox1.List = Range("A2:A50").Value. It answers two questions at once — which item via .ListIndex (0-based, -1 when the text is not in the list) and what text via .Value. The trap: .Value <> "" only proves the box is not empty, not that the user picked a real item. If you need a valid list choice, test .ListIndex >= 0, or forbid typing with .Style = fmStyleDropDownList.

' Read a ComboBox choice - insist it comes from the list
Private Sub cmdOK_Click()
    If ComboBox1.ListIndex = -1 Then
        MsgBox "Please choose an item from the list."
        Exit Sub
    End If
    MsgBox "You chose " & ComboBox1.Value       ' a real, list-backed choice
End Sub

A ComboBox is a control on a UserForm, and it follows the same rule as every other control: you read the choice through a property, not off the box itself. The difference from a ListBox is that a ComboBox shows one line and can accept typing — which is exactly where the interesting bug lives.

What you'll learn

  • Why a ComboBox is a ListBox collapsed to one line, and reads almost the same way
  • Filling it with .List = Range.Value in one shot, or .AddItem for computed items
  • The two questions it answers: which row (.ListIndex) and what text (.Value)
  • The .Style property — fmStyleDropDownCombo lets the user type, fmStyleDropDownList does not
  • The number-one trap: .Value <> "" is not a valid pick; test .ListIndex >= 0
  • When a ComboBox is the right control and when a ListBox fits better

The mental model: a ListBox on one line that also lets you type

A ComboBox is a ListBox folded into a single line with a droparrow. You fill it the same way, and you read it with the same two properties — but because it can also accept free text, it always answers two questions at once:

ComboBox1.ListIndex   ' WHICH row is chosen (0-based, -1 = text is not in the list)
ComboBox1.Value       ' WHAT text is in the box (a list item, OR whatever was typed)

Hold those two apart and the control makes sense. .Value is what the box says. .ListIndex is whether that came from the list. When the user picks from the dropdown, both agree. When the user types something new, .Value holds the typed text and .ListIndex is -1 — the signal that the text is not a list item.

Filling a ComboBox

Filling is identical to a ListBox. Assign a whole column at once, which is the fast path:

Private Sub UserForm_Initialize()
    ComboBox1.List = Sheet1.Range("A2:A50").Value   ' one shot from a column
End Sub

Range.Value is a 2-D array and .List accepts one, so a single line loads the dropdown (see VBA Range). Use .AddItem only for items built one at a time:

Dim i As Long
For i = 1 To 12
    ComboBox1.AddItem "Month " & i     ' add computed items individually
Next i

Call ComboBox1.Clear to empty it before refilling. If you want the box to start showing a default, set .ListIndex = 0 after filling, or assign .Value to a known item.

Style: decide whether the user may type

This is the property people miss. A ComboBox has a .Style that controls its whole character:

ComboBox1.Style = fmStyleDropDownCombo   ' default: pick from the list OR type your own
ComboBox1.Style = fmStyleDropDownList    ' locked: the user can ONLY pick a list item

fmStyleDropDownCombo is the default — the user can type anything. fmStyleDropDownList turns the ComboBox into a strict picker: typing only jumps to matching items, and the final value is always one of yours. If your form must not accept free text, set the .Style — do not try to validate it away after the fact.

The number-one trap: a non-empty box is not a valid pick

Here is the bug that lets bad data through. With the default style, the user can type a value that is not in your list. If your code only checks that the box is not empty, it happily accepts the typo:

' WRONG - accepts anything the user typed
If ComboBox1.Value <> "" Then ProcessOrder ComboBox1.Value

' RIGHT - insist the choice came from the list
If ComboBox1.ListIndex >= 0 Then
    ProcessOrder ComboBox1.Value
Else
    MsgBox "Please choose an item from the list, not free text."
End If

.Value <> "" proves only that something is in the box. .ListIndex >= 0 proves the user chose an actual list item — because .ListIndex is -1 whenever the text is typed rather than picked. The rule is simple: when a valid list choice matters, test .ListIndex, never .Value <> "". If free text should never be possible at all, the cleaner fix is upstream — set .Style = fmStyleDropDownList and the trap cannot occur. (There is also a .MatchRequired property that rejects a typed non-match when the box loses focus, but setting the .Style is the blunter, surer tool.)

Value versus Text, and reacting to a change

For a ComboBox, .Value and .Text usually read the same string. The one you will use is .Value. .Text matters when you care about what is displayed while typing, before the entry is committed. To react the instant the user changes the selection, handle the Change event:

Private Sub ComboBox1_Change()
    ' fires on every keystroke and every pick - keep it light
    Label1.Caption = "Current: " & ComboBox1.Value
End Sub

Because Change fires on every keystroke in a typeable ComboBox, keep that handler cheap — do not run a database lookup on each character.

ComboBox or ListBox? Choose on space and multiplicity

A ComboBox and a ListBox share their fill and read properties, so pick on how the choice should behave. Use a ComboBox when the answer is a single value, space is tight, and a collapsed dropdown is tidier — or when you want to allow free typing. Use a ListBox when the options should be visible at a glance or the user may pick more than one; multi-select is a ListBox-only feature. A ComboBox can never return several items.

How ExcelMaster helps

The quiet failure here is a ComboBox that accepts whatever the user types because the code checked .Value <> "" instead of .ListIndex >= 0 — so a misspelled product name flows straight into your data.

ExcelMaster lets you describe the control in plain words — "a dropdown of product codes the user must pick from, no free text" — and it sets the right .Style, fills the list from your data, and reads the choice by testing .ListIndex so only real selections get through. You keep the workbook and the code.

Frequently asked questions

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

Assign the range to .List in one statement: ComboBox1.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, and call .Clear before refilling a box that already has rows.

How do I get the selected value from a VBA ComboBox?

Read .Value for the text of the current entry and .ListIndex for the 0-based row it came from. .ListIndex is -1 when the text was typed rather than chosen from the list, so if you require a real list choice, test .ListIndex >= 0 before you act on .Value.

How do I stop the user typing free text in a ComboBox?

Set ComboBox1.Style = fmStyleDropDownList. That locks the control to list items only — typing merely jumps to matching entries and the final value is always one of yours. The default, fmStyleDropDownCombo, allows free typing, which is why validating with .ListIndex >= 0 matters when you keep it.

What does ListIndex = -1 mean on a ComboBox?

It means the current text is not one of the list items — either the box is empty or the user typed something that does not match. A valid pick from the dropdown always gives a .ListIndex of 0 or greater, so .ListIndex = -1 is your signal to reject the entry or prompt again.

What is the difference between a ComboBox and a ListBox?

A ComboBox shows one line with a dropdown, returns a single value, and can allow free typing; a ListBox shows several rows at once and can allow multiple selections. Choose a ComboBox for a compact single choice, a ListBox when options must be visible or the user picks more than one.

Tested in

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

Related guides: VBA ListBox · VBA CheckBox · VBA UserForm · VBA Range · VBA InputBox