excelavon

Excel VBA Input Box Dropdown List: Options and Setup

VBA & macros · Updated November 3, 2026

Excel VBA Input Box Dropdown List: Options and Setup

Excel’s built-in VBA InputBox does not support a dropdown list. To let someone choose from a visible list, create a VBA UserForm with a ComboBox; for a quick, simpler prompt, use Application.InputBox and validate the text they enter.

Why the built-in InputBox has no dropdown

The VBA InputBox function displays a message, a text field, and buttons. Application.InputBox is a separate Excel method that can restrict the kind of response—for example, to text, a number, or a worksheet range—but it does not add a list of choices to the dialog. Its Type argument controls the expected response type, not the appearance of the prompt.

This distinction matters because “InputBox” can refer to two different tools. The VBA function returns text (or an empty string when the user cancels), while Excel’s Application.InputBox can return different data types. Neither has a dropdown property. A dropdown requires a control designed to display choices, such as a ComboBox on a UserForm or a data-validation list on a worksheet.

  • VBA InputBox: a basic text prompt, suitable for a short question.
  • Application.InputBox: a prompt that can request a particular type of entry, including a range selection.
  • UserForm ComboBox: a custom dialog control that presents selectable options.
  • Worksheet data-validation list: a dropdown displayed in a cell rather than in a dialog.

Best option for a real dropdown: a UserForm

A UserForm is the usual choice when the user must pick from a controlled set of options. Add a ComboBox to the form, fill it with the allowed values, and add buttons to confirm or cancel. Set the ComboBox’s Style property to 2 - fmStyleDropDownList if users must select an item rather than type an arbitrary value. If typing and suggestions are desirable, use the editable dropdown style instead.

For example, a workbook might ask a staff member to choose a department. A dropdown makes the permitted values visible and avoids differences such as “Customer Support,” “Support,” and “support” that can complicate later reporting. A UserForm also gives you room for instructions, a second field, or validation messages.

Build a basic dropdown form

In desktop Excel for Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > UserForm, then add a ComboBox and two CommandButtons from the Toolbox. In the Properties window, name them cboChoice, cmdOK, and cmdCancel. Set the button captions to “OK” and “Cancel,” and set the form’s name to frmChoice. Add a Label if you want to show instructions.

Double-click the form and place this code in its code module. The example loads three fixed choices, prevents confirmation without a selection, and treats both the Cancel button and the window’s close button as cancellation.

Option Explicit

Public SelectedValue As String
Public WasCancelled As Boolean

Private Sub UserForm_Initialize()
    ' Start in the cancelled state and load the permitted choices.
    WasCancelled = True
    Me.cboChoice.Style = fmStyleDropDownList
    Me.cboChoice.Clear
    Me.cboChoice.AddItem "Sales"
    Me.cboChoice.AddItem "Support"
    Me.cboChoice.AddItem "Finance"
End Sub

Private Sub cmdOK_Click()
    ' Require a choice before returning a value.
    If Me.cboChoice.ListIndex = -1 Then
        MsgBox "Select a department first.", vbExclamation
        Exit Sub
    End If

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

Private Sub cmdCancel_Click()
    ' Hide the form so the calling procedure can read its status.
    WasCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Treat the title-bar close button as Cancel.
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        WasCancelled = True
        Me.Hide
    End If
End Sub

Then add a standard module with a procedure to show the form and use the returned selection. The form is shown modally, so the procedure waits until the user closes it.

Option Explicit

Public Sub AskForDepartment()
    ' Show the picker and use its result unless the user cancelled.
    Dim picker As frmChoice

    Set picker = New frmChoice
    picker.Show vbModal

    If Not picker.WasCancelled Then
        MsgBox "Selected department: " & picker.SelectedValue, vbInformation
    End If

    Unload picker
    Set picker = Nothing
End Sub

Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm), and only enable macros in files you trust. The form and module code must be in the same workbook project for this example to work.

When a text prompt is enough

If a visible dropdown is not essential, an Application.InputBox can ask for one of several named values and then check the response. This is quicker to create than a UserForm, but it relies on the person typing a valid answer. The example below accepts the three department names without regard to letter case or extra spaces. Any other entry produces a reminder rather than being silently accepted.

Option Explicit

Public Sub AskForDepartmentByText()
    ' Request text, then validate it against the allowed choices.
    Dim answer As Variant

    answer = Application.InputBox( _
        Prompt:="Type Sales, Support, or Finance.", _
        Title:="Choose a department", Type:=2)

    ' Application.InputBox returns Boolean False when Cancel is clicked.
    If VarType(answer) = vbBoolean Then Exit Sub

    Select Case LCase$(Trim$(CStr(answer)))
        Case "sales"
            MsgBox "You chose Sales.", vbInformation
        Case "support"
            MsgBox "You chose Support.", vbInformation
        Case "finance"
            MsgBox "You chose Finance.", vbInformation
        Case Else
            MsgBox "Enter Sales, Support, or Finance.", vbExclamation
    End Select
End Sub

This method is useful for a one-off macro where the choices are few and easy to remember. It is less reliable when values are long, similar, or frequently mistyped. For a text prompt, the Type argument is 2; it does not turn the prompt into a dropdown. Also, do not confuse this with Type 8, which asks the user to select a worksheet range. Range selection is useful for choosing cells, but it is not a list of named options.

Choosing between a UserForm and a worksheet list

The best interface depends on where the choice belongs. If the answer is part of a row of worksheet data, a data-validation dropdown is often simpler than opening a separate dialog. Select the target cell or cells and use Data > Data Validation; choose List under Allow, then provide a source such as a range containing the allowed values. The user can then select an item directly in the cell.

A UserForm makes more sense when a macro needs to collect a choice before it performs an action, such as selecting a report type before generating a report. It can also combine a dropdown with other fields and buttons. An Application.InputBox is the lightest option when free-form text or a range selection is actually what the task requires.

  • Choose a UserForm ComboBox for a guided dialog with a defined set of choices.
  • Choose data validation when the choice should be stored or edited in a worksheet cell.
  • Choose Application.InputBox for a typed response or range selection, not a dropdown.
  • Choose the basic InputBox function only when a simple text question is sufficient.

Common problems and fixes

If the form opens but its list is empty, confirm that the code is in the form’s module and that the ComboBox is actually named cboChoice. Control names in code must match the names in the Properties window. Check that the UserForm_Initialize event has that exact spelling; a differently named procedure will not run automatically when the form opens.

If the OK button appears to do nothing, confirm that its control name matches the event procedure name. For a button named cmdOK, the handler must be cmdOK_Click. Also check whether the user has selected an item: with fmStyleDropDownList, the user cannot enter a new value, and the example intentionally refuses to proceed when no list item is selected.

If cancelling an Application.InputBox causes an error, account for its cancellation result. With a text-type prompt, the sample checks whether the returned Variant is Boolean before converting it to text. For a range-type prompt, the return value is an object reference when a range is chosen, so code must handle the cancel case and avoid assuming that a valid Range was returned.

Finally, macro settings can prevent any VBA code from running. A workbook saved as .xlsx does not retain VBA project code; use .xlsm for a macro-enabled workbook. Do not lower security settings indiscriminately to make a file run. Follow your organization’s policy and use trusted workbooks.

Windows, Mac, and Excel for the web

The examples target desktop Excel for Microsoft 365 on Windows. The Visual Basic Editor shortcut shown above is for Windows; on Mac, the editor can generally be opened with Option+F11 (some keyboards require Fn as well), or from the Developer tab. Ribbon and keyboard details can vary with keyboard settings and Excel release.

Excel for the web does not run VBA macros, so neither the UserForm nor the InputBox procedures can be used there. A workbook can still contain VBA when opened in the web app, but running or editing that VBA requires desktop Excel. Excel for Mac supports VBA, but its support for controls and form behavior differs from Windows; ActiveX controls are not supported on Mac. If a workbook must work across operating systems, test the UserForm on the actual target versions and consider worksheet data validation as a simpler cross-platform interface.

Frequently Asked Questions

Can I put a dropdown directly inside VBA InputBox?
No. Neither the VBA InputBox function nor Application.InputBox provides a dropdown control. Use a UserForm ComboBox for a dialog dropdown, or a worksheet data-validation list for a cell dropdown.

What is the easiest way to show choices in a VBA dialog?
Create a UserForm, add a ComboBox, populate it with the allowed values, and show the form with vbModal. For only a few simple values, a text prompt with validation takes less setup but does not display selectable options.

Can Application.InputBox select a cell range?
Yes. Its Type argument can request a range selection (Type 8). That lets a user select cells on a worksheet; it does not create a dropdown of choices.

Can I use a UserForm dropdown in Excel for the web?
No. Excel for the web does not execute VBA. Use desktop Excel to run the macro, or use a worksheet dropdown created with Data Validation if the workbook needs a choice interface in the browser.

Why does my dropdown allow typing?
Check the ComboBox’s Style property. Set it to 2 - fmStyleDropDownList to restrict the control to listed items. An editable ComboBox style allows users to type a value that may not be in the list.

Related

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.