Excel VBA Input Box With a Drop-Down List

Excel’s built-in VBA InputBox cannot display a drop-down list. To let someone choose from fixed options, create a VBA UserForm with a ComboBox set to drop-down-list style; the example below shows how to build it, return the choice, and handle cancellation.

Why the built-in InputBox cannot show a list

VBA’s InputBox function displays a prompt and a text field. A person can type a response, but the function has no option for adding a ComboBox, a list of choices, or other custom controls. Excel’s Application.InputBox is a separate method that can restrict the kind of value returned, such as a number or a cell reference, but it also does not provide a drop-down menu.

This is an easy point to misunderstand because both features are called an input box. If the user must choose an approved value—such as a department, region, status, or product category—a UserForm is the usual VBA solution. It is a small custom window that can contain controls, validation, instructions, and buttons.

Choose a ComboBox or a ListBox

A ComboBox is normally the best fit when the form should take up little space. In its editable style, it can show suggestions while still allowing typed text. Set its Style property to fmStyleDropDownList when users must pick only from the choices you provide. The control then behaves like a drop-down list rather than an open text field.

A ListBox shows several options at once, which can be useful when the list is short or when selecting multiple items is allowed. For a single choice from a longer set, a ComboBox is usually more compact. Neither control is the built-in InputBox; both are controls placed on a UserForm.

Create the UserForm and controls

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > UserForm. If the Toolbox is not visible, use View > Toolbox. Select the form and change its (Name) property to frmChoice; its Caption property can be something readable, such as “Choose a region.”

Add a ComboBox, a CommandButton for confirming, and another CommandButton for cancelling. Set their (Name) properties to cboChoice, cmdOK, and cmdCancel. Set the button captions to “OK” and “Cancel.” The names matter because the code refers to the controls by those names. A label above the ComboBox can clarify what the user is choosing.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a macro-enabled workbook must be saved in a format that supports macros, such as .xlsm. Only enable macros in files you trust.

Add choices and handle the buttons

Double-click the UserForm to open its code window, then add the following form code. This example offers three fixed choices. The form starts in a cancelled state, so closing it without confirming does not accidentally return a selection.

Option Explicit

Public WasCancelled As Boolean
Public SelectedChoice As String

Private Sub UserForm_Initialize()
    ' Load the choices when the form opens.
    With Me.cboChoice
        .Clear
        .Style = fmStyleDropDownList
        .AddItem "North"
        .AddItem "Central"
        .AddItem "South"
        .ListIndex = -1
    End With

    WasCancelled = True
    SelectedChoice = vbNullString
End Sub

Private Sub cmdOK_Click()
    ' Require a choice before closing with OK.
    If Me.cboChoice.ListIndex = -1 Then
        MsgBox "Select a region before continuing.", vbExclamation
        Exit Sub
    End If

    SelectedChoice = CStr(Me.cboChoice.Value)
    WasCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    ' Keep the cancelled state and close the form.
    WasCancelled = True
    Me.Hide
End Sub

UserForm_Initialize runs when the form is created. .AddItem adds each option, and .ListIndex = -1 leaves the list unselected initially. The OK event checks the selection instead of accepting a blank value. Hiding the form preserves its public result properties long enough for the calling procedure to read them; the caller can unload it afterward.

Show the form and retrieve the selection

Put this procedure in a standard module. In the Visual Basic Editor, choose Insert > Module, then paste the code. The form is shown modally, so the user must respond to it before continuing with the rest of the procedure.

Option Explicit

Public Sub AskForRegion()
    Dim picker As frmChoice

    ' Create and display the selection form.
    Set picker = New frmChoice
    picker.Show vbModal

    If Not picker.WasCancelled Then
        MsgBox "Selected region: " & picker.SelectedChoice, vbInformation
    End If

    ' Release the form after reading its result.
    Unload picker
    Set picker = Nothing
End Sub

Run AskForRegion from the editor or assign it to a worksheet button. If the user selects “Central” and clicks OK, the message displays “Selected region: Central.” If the user clicks Cancel, the procedure skips the message. Closing the form with its window close button also leaves WasCancelled true because that is the initial state.

Load options from a worksheet

Hard-coded choices are fine for a short list that rarely changes. If the options change over time, keep them in a worksheet instead. For example, put a heading in cell A1 and the choices in Lists!A2:A downward. Replace the earlier UserForm_Initialize procedure with the version below; retain the same button event procedures.

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    ' Read nonblank options from the Lists worksheet.
    Set ws = ThisWorkbook.Worksheets("Lists")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With Me.cboChoice
        .Clear
        .Style = fmStyleDropDownList

        If lastRow >= 2 Then
            For Each cell In ws.Range("A2:A" & lastRow).Cells
                If Len(Trim$(CStr(cell.Value))) > 0 Then
                    .AddItem CStr(cell.Value)
                End If
            Next cell
        End If

        .ListIndex = -1
    End With

    WasCancelled = True
    SelectedChoice = vbNullString
End Sub

This version skips blank cells and does not treat the heading in A1 as a choice. The worksheet must exist in the same workbook and be named exactly Lists; otherwise, the code will stop with a VBA error. If there are no options, the form opens with an empty list, and the OK validation prevents a selection from being accepted. For a workbook used by other people, consider showing a clear message when the source list is empty.

Common problems and practical safeguards

The code cannot find a control: Check the control’s (Name) property, not its displayed caption. cboChoice and ComboBox1 are different names.
The form accepts typed text: Confirm that the ComboBox style is fmStyleDropDownList. The editable ComboBox style permits text that is not in the list.
OK returns an empty value: Keep the ListIndex validation. It checks that an actual list item was selected before the form is hidden.
The list is empty or incomplete: Check the worksheet name, starting row, and column. In the worksheet-based example, options must be in column A starting at A2, with no data below the last used choice that could confuse the intended list.
Cancel still appears to return a value: The calling procedure must check WasCancelled before using SelectedChoice. A cancelled result should not be treated as a valid selection.

For important data entry, validate the returned value again where it is used. A form improves the user interface, but it does not make workbook data immune to later edits or unexpected changes. It is also sensible to keep the source choices in one clearly labelled place rather than scattering the same options across several procedures.

Alternatives and platform limits

If you do not need a pop-up window, Excel’s Data Validation list can provide a drop-down directly in a worksheet cell. On Windows, select the target cell and choose Data > Data Validation; set Allow to List and specify the source. This is often simpler for ordinary worksheet entry, while a UserForm is more suitable when a macro needs to guide a sequence of choices or validate several fields together.

A plain VBA InputBox remains useful when any text response is acceptable. For example, a prompt asking for a short comment does not need a ComboBox. If the answer must match one of a small set of approved values, do not rely on instructions in the prompt alone; use a list control or worksheet validation.

VBA macros do not run in Excel for the web, so a workbook that depends on this form must be opened in desktop Excel. Excel for Mac supports VBA, but the Visual Basic Editor, keyboard shortcuts, and available controls can differ from Windows; check the form and controls in the target Mac version before distributing a workbook. On Windows, Alt+F11 opens the editor. On Mac, the shortcut may depend on the keyboard and function-key settings, so opening the Visual Basic Editor from Excel’s macro or Tools menu is an alternative.

Frequently Asked Questions

Can I put a drop-down list in VBA InputBox?

No. The built-in VBA InputBox accepts typed text and does not support adding a ComboBox. Create a UserForm with a ComboBox when users need to select from a list.

Is Application.InputBox different from InputBox?

Yes. Application.InputBox can request certain kinds of input, including a number or a range reference, but it still does not display a customizable drop-down list. Use a UserForm for that interface.

How do I stop users typing a value that is not in the list?

Set the ComboBox Style property to fmStyleDropDownList. Also check that a list item is selected in the OK button event before accepting the result.

Can the drop-down choices come from cells?

Yes. Read the worksheet range in UserForm_Initialize and add each nonblank cell with the ComboBox’s AddItem method. The worksheet-based example above uses values from Lists!A2 downward.

Will a VBA UserForm work in Excel for the web?

No. Excel for the web does not run VBA macros or display VBA UserForms. Use desktop Excel for a VBA solution, or use a worksheet Data Validation list when a cell-based drop-down is sufficient.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.