No: VBA’s built-in InputBox does not have a dropdown control, and Excel’s Application.InputBox does not add one. To let someone choose from a list in a dialog, create a VBA UserForm with a ComboBox; for a dropdown directly on a worksheet, use Data Validation instead.
What the built-in InputBox can and cannot do
The standard VBA InputBox displays a prompt, a text-entry field, and buttons. It is useful when the person running a macro needs to type a name, number, or other short value. It has no property for supplying a list of choices, so there is no argument such as Items or Dropdown that turns its text field into a menu.
Excel’s Application.InputBox is a related but distinct function. Its Type argument can restrict the kind of response Excel accepts, such as text, a number, a formula, or a cell reference. That restriction is not a dropdown: the user still enters or selects a response rather than choosing an item from a list presented in the prompt. The distinction matters because examples online sometimes call both functions simply “InputBox.”
A dropdown is useful when answers must match a known set of values. It helps prevent spelling variations, makes later comparisons more reliable, and lets users see which options are permitted. If you only need a quick text prompt, an ordinary InputBox is simpler; if the set of choices matters, use a control designed to display a list.
Use a UserForm ComboBox for a dialog dropdown
A UserForm is VBA’s customizable dialog window. Add a ComboBox to it, fill that control with the permitted choices, and show the form when the macro runs. Unlike the built-in InputBox, the form can also contain labels, instructions, and multiple controls. This is generally the closest match to a request for an “InputBox with a dropdown.”
For a fixed list, set the ComboBox’s Style property to 2 - fmStyleDropDownList. That displays a list but prevents the user from typing an unlisted value. If users should be allowed to enter a new value as well as choose an existing one, leave the style at its editable setting instead. Pick the behavior deliberately: an editable ComboBox can return a value that was never in the original list.
To set up a basic form in Excel for Microsoft 365 on Windows:
- Open the Visual Basic Editor from the Developer tab’s Visual Basic command. In the editor, choose Insert > UserForm.
- In the Toolbox, add a ComboBox and set its (Name) property to
cboChoice. Add CommandButtons namedcmdOKandcmdCancel, and set their captions to OK and Cancel. - Set the ComboBox’s
Styleproperty to2 - fmStyleDropDownListif only listed choices should be accepted. - Open the form’s code window and add the code below. Change the sample options to the values your workbook needs.
- Put the calling procedure in a standard module, then run it to display the form.
The code treats Cancel and closing the form with its window close button as cancellation. It also starts with the first choice selected; remove the .ListIndex = 0 line if you want the user to make an explicit selection.
Option Explicit
Public SelectedValue As String
Public WasCancelled As Boolean
Private Sub UserForm_Initialize()
' Start in the safe state: no confirmed choice yet.
WasCancelled = True
With Me.cboChoice
.Clear
.AddItem "North"
.AddItem "South"
.AddItem "East"
.AddItem "West"
.ListIndex = 0
End With
End Sub
Private Sub cmdOK_Click()
If Me.cboChoice.ListIndex < 0 Then
MsgBox "Choose an item before continuing.", vbExclamation
Exit Sub
End If
SelectedValue = CStr(Me.cboChoice.Value)
WasCancelled = False
Me.Hide
End Sub
Private Sub cmdCancel_Click()
' WasCancelled remains True.
Me.Hide
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
' Keep the form instance available so the caller can read its status.
Cancel = True
Me.Hide
End If
End Sub
Return the chosen value to the macro
Hiding the form on OK preserves its public values long enough for the calling procedure to read them. The caller can then use the selected value in a calculation, write it to a cell, or branch to different actions. For example, if the form’s name is frmPicker, this standard-module procedure displays it modally, meaning the macro waits until the form is hidden before continuing:
Option Explicit
Public Sub AskForRegion()
Dim picker As frmPicker
Set picker = New frmPicker
picker.Show vbModal
If picker.WasCancelled Then
MsgBox "No region was selected.", vbInformation
Else
MsgBox "Selected region: " & picker.SelectedValue, vbInformation
End If
Unload picker
Set picker = Nothing
End Sub
Replace the message boxes with the work your macro needs to perform. For example, inside the non-cancelled branch, you could put the selected region in a worksheet cell. If the selected value will be used in a lookup or comparison, make sure the options match the values stored in the workbook, including spaces and punctuation. A dropdown standardizes the response, but it cannot correct inconsistent source data.
When worksheet Data Validation is a better fit
If the user should choose in a cell rather than in a pop-up dialog, use Excel’s in-cell dropdown. Select the target cell or range, then choose Data > Data Validation. On the Settings tab, set Allow to List, provide the list in Source, and leave In-cell dropdown enabled. The source can be a short comma-separated list or a range of cells containing the choices.
For a list that may change, a range on a separate worksheet is often easier to maintain than typing every option into the Source box. Keep the source values together, update them in one place, and point the validation rule to that range. If the list is intended to grow, plan how the source range will include new entries; a validation rule pointing only to a fixed set of cells will not automatically include items added outside that range.
Data Validation is not a VBA InputBox. It puts the choice in a worksheet cell and applies a rule there; it does not create a custom dialog or automatically pause a macro for an answer. It is often the more suitable approach when users are filling out a worksheet, because the chosen value remains visible and can be edited later. Excel for the web can work with worksheet data validation, but it does not run VBA macros.
InputBox, Application.InputBox, UserForm, or cell dropdown?
These options solve different problems, so choose based on where the response belongs and how strict the choice must be.
- VBA
InputBox: Use it for a quick, free-form text response when a custom interface is unnecessary. It does not offer preset dropdown choices. - Excel
Application.InputBox: Use it when the response needs to be a particular kind of input, such as a number or a range reference. Its type setting validates the response category; it does not display a list of choices. - UserForm ComboBox: Use it when a macro needs to ask a question in a dialog and the person should select from a list. It takes more setup, but the layout and behavior are customizable.
- Data Validation list: Use it when the response should be entered into a worksheet cell. It is usually simpler to maintain than building a dialog just to collect one value.
A worksheet ComboBox is another possibility, but it adds a control to the sheet rather than creating an InputBox-style dialog. For a straightforward list in a cell, Data Validation is usually easier to share and maintain. Choose a form control only when the extra interface behavior is worth the added setup.
Common problems and practical safeguards
If a form opens with an empty list, check that the initialization code is in the UserForm’s code module, not in a standard module, and confirm that the control is actually named cboChoice. VBA uses the control’s (Name) property in code; changing only its displayed text or label does not change that name. Also verify that the options are added before the code assigns a list index.
If the OK button appears to do nothing, check its (Name) property and confirm that the event procedure name matches it. A button called CommandButton1 will not run cmdOK_Click unless you rename the control or use the matching event procedure. If the selected value is blank, check that the list contains items and that the form has not been hidden before the OK handler stores the value.
Cancellation needs explicit handling. With the standard VBA InputBox, pressing Cancel returns a zero-length string, which can be difficult to distinguish from the user submitting an empty response. A UserForm lets you track cancellation separately, as the WasCancelled flag does above. Do not assume that a value in the ComboBox means the user confirmed it: the form may close through Cancel or its close button.
Before running a macro that changes workbook data, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken write or deletion may not be recoverable through Excel’s normal Undo command. Save macro-enabled workbooks in a format that preserves VBA, such as .xlsm, and run macros only when you trust their source. Excel for Mac has a different interface and may differ in control availability or behavior, so check the specific form and controls in the Mac version you use; Excel for the web cannot execute VBA.
Frequently Asked Questions
Can I add a dropdown to VBA’s InputBox?
No. The built-in InputBox has a text field, not a ComboBox, and it has no setting that converts the field into a dropdown. Use a UserForm ComboBox for a dialog list or Data Validation for a dropdown in a worksheet cell.
Does Application.InputBox have a list of choices?
No. Its Type argument can limit the kind of answer, but it does not present preset items in a dropdown. Use a UserForm if the user must select one of several displayed options.
How do I stop users typing a value that is not on the list?
On a UserForm ComboBox, set Style to 2 - fmStyleDropDownList and check that a list item is selected before accepting the form. For an in-cell dropdown, configure Data Validation to allow a list and use its error alert to reject invalid entries.
Can a UserForm dropdown use values from worksheet cells?
Yes. You can load a range’s values into the ComboBox when the form initializes instead of writing each option with AddItem. That is useful for a list maintained by workbook users; make sure the range is correct and decide how blank cells should be handled.
Will VBA dropdowns work in Excel for the web?
No VBA macro or VBA UserForm runs in Excel for the web. Users can interact with worksheet features such as Data Validation there, but a macro-driven dialog requires a desktop version of Excel that supports the workbook’s VBA code.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Subscript Out of Range: Causes and FixesExcel VBA “Run-time error 9: Subscript out of range” means your code asked for an item that does…
- VBA Dictionary Get Value by Key: Syntax and ExamplesTo get a value from a VBA Scripting.Dictionary, use its key as the item index: value = dict.Item(key)…
- How to Code Toggle Buttons in VBATo code a toggle button in Excel VBA, insert an ActiveX Toggle Button, open its worksheet code window,…
- Excel VBA Function Parameters: Defaults, ByRef, and MoreIn Excel VBA, a standard function parameter is a named input declared in the function’s parentheses, such as…
- Excel Macro to Email Files From a Folder AutomaticallyIn Excel for Microsoft 365 on Windows, a VBA macro can let you choose a folder, attach its…
- VBA Code to Insert an Image in an Excel Cell AutomaticallyUse VBA’s Shapes.AddPicture method to insert a picture, then position and size it to match a worksheet cell.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.