Excel VBA InputBox Dropdown List: What Works Instead

No: Excel VBA’s standard InputBox cannot display a dropdown list of choices. To offer a real dropdown, use a VBA UserForm with a ComboBox, or put a dropdown in a worksheet cell with Data Validation and have your macro read that cell.

Can a VBA InputBox show a dropdown?

The familiar VBA InputBox and Excel’s Application.InputBox are prompts for entering or selecting a value, not dialog boxes with configurable choice lists. You can set a prompt, title, and default value, but there is no argument that supplies a list of dropdown items. Asking users to type one of several options is possible, but it does not provide the selection control many people mean by “InputBox dropdown.”

The two similarly named methods have different return behavior and capabilities. VBA’s InputBox returns text, while Application.InputBox can request a particular kind of input, such as text, a number, or a range. For example, its Type argument can make Excel ask the user to select a range on a sheet. That range-selection mode is not a dropdown, and neither method can turn a list of labels into selectable menu items.

For a short-lived prompt with a fixed set of options, a UserForm ComboBox is the closest match. If the choice belongs in the workbook and can remain visible in a cell, Data Validation is usually simpler.

Best option for a dialog: a UserForm ComboBox

A UserForm is a custom dialog created in the Visual Basic Editor. Add a ComboBox to it, populate the list when the form opens, and use buttons to accept or cancel the selection. Set the ComboBox’s style to a dropdown list so users select an existing item rather than typing an arbitrary value.

This approach is useful when a macro needs a choice before it continues—for example, asking which department’s report to create, which worksheet to process, or which shipping method to use. The form can also contain instructions, labels, and other controls. It takes more setup than a built-in InputBox, but it gives you control over the choices and how cancellation is handled.

Create the form and add its controls

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, the editor shortcut is usually Option+F11; some keyboards require Fn+Option+F11, or you can open the editor from Excel’s Developer tab. Excel for the web does not run VBA macros or provide the VBA editor.

In the Visual Basic Editor, choose Insert > UserForm. In the Properties window, set the form’s (Name) to frmChoice and its Caption to something like Choose a region.
Use the Toolbox to add a ComboBox, a CommandButton for confirmation, and another CommandButton for cancellation. Set their names to cboChoice, cmdOK, and cmdCancel. Set the buttons’ captions to OK and Cancel.
Double-click the form and add the form code below. The listed options are examples; replace them with the items your workbook needs.

Before running any macro, save a copy of the workbook: VBA actions cannot be undone with Ctrl+Z. Save a macro-enabled workbook as .xlsm if it needs to retain its VBA project.

Populate the dropdown and handle the selection

The form’s Initialize event runs as the form is created. The code adds three choices, prevents free-form typing, and starts with no item selected. The buttons set a cancellation flag and hide the form so the calling macro can inspect the result.

Option Explicit

Private mCancelled As Boolean

Public Property Get WasCancelled() As Boolean
    WasCancelled = mCancelled
End Property

Public Property Get SelectedChoice() As String
    SelectedChoice = CStr(Me.cboChoice.Value)
End Property

Private Sub UserForm_Initialize()
    With Me.cboChoice
        .Clear
        .AddItem "North"
        .AddItem "South"
        .AddItem "West"
        .Style = fmStyleDropDownList
        .ListIndex = -1
    End With
    mCancelled = True
End Sub

Private Sub cmdOK_Click()
    If Me.cboChoice.ListIndex = -1 Then
        MsgBox "Select an option before continuing.", vbExclamation
        Exit Sub
    End If

    mCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    mCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        mCancelled = True
        Me.Hide
    End If
End Sub

The QueryClose event treats clicking the window’s close button as cancellation too. Without that handling, a user could close the form in a way your calling code does not expect. The selection check also prevents the OK button from returning an empty choice.

Show the form from a macro

Add a standard module using Insert > Module, then put this procedure in it. It displays the form modally, meaning the macro waits for the user to respond. If the user accepts, the example displays the chosen value; replace that message with the action your workbook should perform.

Option Explicit

Public Sub AskForChoice()
    Dim dlg As frmChoice
    Dim answer As String

    Set dlg = New frmChoice
    dlg.Show vbModal

    If Not dlg.WasCancelled Then
        answer = dlg.SelectedChoice
        MsgBox "You chose: " & answer, vbInformation
    End If

    Unload dlg
    Set dlg = Nothing
End Sub

Run AskForChoice from the Macro dialog or assign it to a worksheet button. The result is one of the three labels added in the Initialize event, unless the user cancels. To use a longer list, add more .AddItem lines or load the items from a worksheet range. For a range-based list, validate that the source range contains the expected values and is available when the form opens.

Use a worksheet dropdown instead

If a dialog is unnecessary, a cell dropdown is often easier to maintain. Enter the choices in a worksheet range, select the cell where the user should choose, and use Data > Data Validation. In the dialog, set Allow to List and set Source to the choice range. Keep In-cell dropdown enabled, then confirm the settings. The menu path is the same basic Data Validation feature in Excel for Windows and Mac, though dialog layout can vary by release.

A macro can read the selected cell’s value after the user chooses an item. This works well for a small workflow where the choice can sit on a setup sheet or beside other inputs. A named range can make the list source easier to understand and maintain than a hard-coded address. If the list grows, consider placing the choices in an Excel table and checking how the validation source is defined, since list-source behavior can depend on how the range or name is set up.

Unlike a UserForm, a cell dropdown remains part of the worksheet and can be seen, edited, or cleared as workbook data. It is therefore not a substitute for a private or transient dialog. Data Validation can discourage entries outside the list, but it should not be treated as strong security or as proof that a value is valid: pasted or programmatically written data may bypass normal selection behavior. A macro that depends on the value should check it before acting.

Choose the approach that fits the task

Use a UserForm ComboBox when the macro needs a dialog, the user must make a choice before continuing, or you want to combine the dropdown with instructions and buttons.
Use Data Validation when the selection is ordinary worksheet input that users may want to review or change later.
Use a regular InputBox when free-form text or a simple numeric response is appropriate and a dropdown would add unnecessary complexity.

A ComboBox can allow typed text, but setting Style to fmStyleDropDownList limits the user to listed items. That is generally preferable when your macro expects an exact category name. If new values should be allowed, use a typing-enabled ComboBox and validate the entered text before using it. A list of options shown in an ordinary InputBox prompt is not equivalent: the user still has to type a response, and spelling or spacing differences can cause problems.

Common problems and practical checks

If the macro says that a method or control cannot be found, check the UserForm and control names in the Properties window. VBA refers to the control’s (Name), not its visible caption. For instance, the code’s cboChoice reference must match the ComboBox name exactly. Also check that event procedures are in the form’s code module, rather than in a standard module.

If the list appears empty, confirm that the Initialize event is named correctly and that the form is being created and shown as expected. If the OK button seems to do nothing, make sure its control name matches cmdOK and its click event contains the acceptance code. When options come from cells, check that the source range is not blank and that the macro can access the intended worksheet.

Macro availability depends on the workbook and the user’s security settings. The workbook must be saved in a macro-enabled format, and the user may need to enable macros according to their organization’s policy. Do not tell users to lower security globally just to run a workbook. Excel for the web cannot run VBA; users need a desktop version of Excel. Excel for Mac supports VBA, but Windows-only ActiveX worksheet controls are not a portable alternative. If a workbook must work on both platforms, test the specific UserForm controls and workflow on each target version.

Frequently Asked Questions

Can Application.InputBox show a list of options?
No. It can request input such as text, a number, or a range, but it does not offer a configurable dropdown list. Use a UserForm ComboBox or a worksheet cell with Data Validation.

Can I make the ComboBox selection-only?
Yes. Set the ComboBox’s Style property to fmStyleDropDownList, as in the example. Users can then choose a listed item rather than type a different value.

Can the dropdown choices come from worksheet cells?
Yes. A UserForm ComboBox can be populated from a range when the form opens, or a worksheet cell can use Data Validation with a range as its list source. A cell-based list is often simpler when the selection should remain visible in the sheet.

Does a dropdown work in Excel for the web?
Worksheet Data Validation dropdowns are available in Excel for the web, but VBA macros and VBA UserForms do not run there. A workbook that relies on the form needs to be opened in desktop Excel.

Is clicking Cancel the same as choosing a blank item?
Not in the sample. Cancel and the form’s close button set a cancellation flag, while OK requires an actual selection. Keeping those cases separate lets the calling macro avoid treating cancellation as a valid choice.

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.