Excel VBA’s standard InputBox asks users to type a response; it does not create a dropdown or a set of selectable choices. For a short fixed menu, use Application.InputBox with numbered options, use MsgBox for simple Yes/No decisions, or build a UserForm with a ComboBox or ListBox for a polished selection interface.
What “InputBox choice” means in VBA
People searching for an “Excel VBA InputBox choice” may mean one of two things: they want the prompt to offer options, or they want VBA to restrict what the person can enter. The built-in InputBox function does neither. It displays a prompt and a text field, then returns the text the user enters. Your macro must interpret and validate that response.
There are two commonly used VBA input boxes, and their names are easy to confuse:
InputBoxis the VBA function that returns a string. Its arguments let you set the prompt, title, default response, and position.Application.InputBoxis an Excel method with aTypeargument. That argument can restrict the kind of response, such as text, a number, a logical value, or a worksheet range.
Neither version offers a built-in list of custom labels to click. The right solution depends on whether the user is choosing between two outcomes, entering a small menu number, or selecting an item from a longer list.
What the two InputBox versions can and cannot do
The standard InputBox is useful when the user needs to provide a short value such as a name, code, or note. For instance, a macro can ask for a department name and then check that it matches an approved list. The dialog itself, however, will still accept free-form text; validation happens after the user submits it.
Application.InputBox can narrow the response type. Its Type argument supports numbers, text, logical values, ranges, and several other Excel-specific response types. Setting Type:=1, for example, asks for a number. This checks the kind of input, not whether that number is one of your allowed menu choices. A prompt that says “Enter 1, 2, or 3” still needs code to reject 4 or 2.5.
There is also a cancellation detail to consider. The standard InputBox returns an empty string when the user clicks Cancel, and an empty string can also result when the user submits a blank answer. If blank is a valid response and you must distinguish it from Cancel, the standard function does not provide a reliable distinction. Application.InputBox returns Boolean False when canceled, which can be distinguished from a numeric zero by checking the returned Variant’s type.
Choose the interface that fits the decision
For a tiny menu used occasionally, a numbered Application.InputBox keeps the macro self-contained. It is quick to create and does not require designing a form, but users must read the choices and type a number accurately.
For a binary decision, MsgBox is usually clearer. A Yes/No or Yes/No/Cancel dialog gives users labeled buttons instead of asking them to remember what “1” means. It is not a general menu: its button sets are predefined, so it cannot display arbitrary labels such as “North,” “South,” and “Central.”
For multiple named choices, especially a longer list or a choice that must be selected from approved items, a UserForm containing a ComboBox or ListBox is more suitable. It takes more setup, but can show labels directly and can prevent arbitrary entries. A useful rule of thumb is:
- Use a text InputBox for a short free-form answer.
- Use a numeric Application.InputBox for a small, stable menu.
- Use MsgBox buttons for a simple decision with standard outcomes.
- Use a UserForm when users need to select among several meaningful labels.
Create a numbered choice menu with Application.InputBox
This example presents three choices, detects Cancel, rejects values outside the menu, and then reports the selected action. The answer is stored as a Variant because Application.InputBox can return different types, including Boolean False on cancellation. Checking VarType first avoids treating a valid numeric zero as Cancel.
Sub ChooseReportType()
Dim answer As Variant
' Ask for a number; Cancel returns Boolean False.
answer = Application.InputBox( _
Prompt:="Choose a report:" & vbCrLf & _
"1 = Sales" & vbCrLf & _
"2 = Expenses" & vbCrLf & _
"3 = Summary", _
Title:="Report choice", Type:=1)
If VarType(answer) = vbBoolean Then
If answer = False Then Exit Sub
End If
If answer <> 1 And answer <> 2 And answer <> 3 Then
MsgBox "Enter 1, 2, or 3.", vbExclamation, "Invalid choice"
Exit Sub
End If
Select Case answer
Case 1
MsgBox "Sales report selected."
Case 2
MsgBox "Expenses report selected."
Case 3
MsgBox "Summary report selected."
End Select
End Sub
The code uses line continuations so the prompt is readable in the VBA editor. It only reports a selection; replace the three messages with the actual procedures that create or display each report. Keep validation before those actions, so an unexpected value cannot accidentally trigger the wrong branch. Before running any macro that changes a workbook, save a copy: VBA changes cannot be reversed with Ctrl+Z.
Use MsgBox for Yes, No, or Cancel
If the user is deciding whether to continue, a button-based message is usually easier to understand than a numbered prompt. MsgBox returns a value of the VbMsgBoxResult enumeration, which you can test in a Select Case statement. This example distinguishes Yes, No, and Cancel:
Sub ConfirmUpdate()
Dim choice As VbMsgBoxResult
choice = MsgBox("Update the selected records?", _
vbYesNoCancel + vbQuestion, "Confirm update")
Select Case choice
Case vbYes
MsgBox "Continue with the update."
Case vbNo
MsgBox "The update was not started."
Case vbCancel
Exit Sub
End Select
End Sub
The constants matter: test for vbYes, vbNo, and vbCancel, rather than assuming the return is a simple position number. Use button combinations that make sense for the action. For example, don’t make “No” mean “delete”; describe the consequence in the prompt and use a confirmation step for destructive operations.
Offer named options with a UserForm
A UserForm is the VBA option when the prompt needs a genuine selectable list. In the Visual Basic Editor, insert a UserForm, add a ComboBox named cboChoice, and add command buttons named cmdOK and cmdCancel. Set the ComboBox’s Style property to 2 - fmStyleDropDownList so users must choose an item instead of typing a different value. Add items when the form opens:
Private Sub UserForm_Initialize()
With cboChoice
.Clear
.AddItem "Sales"
.AddItem "Expenses"
.AddItem "Summary"
.ListIndex = 0
End With
Me.Tag = ""
End Sub
Private Sub cmdOK_Click()
If cboChoice.ListIndex = -1 Then
MsgBox "Select an option first.", vbExclamation
Exit Sub
End If
Me.Tag = "OK"
Me.Hide
End Sub
Private Sub cmdCancel_Click()
Me.Tag = "CANCEL"
Me.Hide
End Sub
The calling macro can show the form, inspect its result, and then unload it. In the example below, replace frmChoice with the actual name shown in the form’s Properties window:
Sub ShowChoiceForm()
Dim selectedChoice As String
Load frmChoice
frmChoice.Show
If frmChoice.Tag = "OK" Then
selectedChoice = CStr(frmChoice.cboChoice.Value)
MsgBox "You selected: " & selectedChoice
End If
Unload frmChoice
End Sub
Forms are more work than a single prompt, but they make the available choices visible and reduce spelling errors. A ListBox is another useful control when you want several options visible at once; a ComboBox takes less screen space. If the available items come from worksheet data, populate the control from that range rather than hard-coding every item, and decide how the form should behave when the source list is empty.
Validate input and handle cancellation safely
A prompt is not validation by itself. A numeric type restriction prevents text such as “blue” from being accepted as a number, but your macro still needs to check permitted values and boundaries. For a numbered menu, check that the answer matches one of the listed choices before running any action. For quantities, check whether fractional or negative values are allowed. For text responses, trim unwanted spaces and compare against the permitted labels in a way that suits the workbook.
Cancellation should be treated as a normal exit path, not as an error. In particular, avoid continuing with a default value after the user clicks Cancel unless that behavior is intentional and clearly explained. If using the standard string-returning InputBox, decide whether an empty answer is allowed; because blank submission and Cancel both return an empty string, choose another interface if that distinction is essential.
Also keep the prompt specific. “Choose a report: 1 = Sales, 2 = Expenses, 3 = Summary” is easier to act on than “Enter option.” If a choice starts a consequential operation, explain what will happen before asking, and keep the Cancel or No path available where appropriate.
Excel version, platform, and macro considerations
VBA macros run in the desktop versions of Excel for Windows and Mac, not in Excel for the web. Excel for the web can open a macro-enabled workbook, but it cannot run or edit its VBA code; users need desktop Excel for that. On Windows, open the Visual Basic Editor with Alt+F11. On Mac, the editor and VBA features are available in desktop Excel, but menus and keyboard shortcuts differ; use the Developer tab or Excel’s menu commands to open the editor if the Windows shortcut does not apply.
UserForms and their controls are part of desktop VBA, but interface details and available controls can vary across platforms and Office installations. If a workbook must be used on both Windows and Mac, test the form in both desktop environments and avoid relying on Windows-only APIs or external controls. For a simple cross-platform prompt, built-in InputBox and MsgBox dialogs are generally less elaborate than a custom form.
Saving the file as a macro-enabled workbook, typically .xlsm, preserves VBA code. Users may also need to allow macros under their organization’s security policy. Do not ask readers to enable macros from an untrusted file: a macro can alter workbook contents or perform other actions available to the workbook’s code.
Frequently Asked Questions
Can an Excel VBA InputBox show a dropdown?
No. The built-in InputBox dialogs do not include a custom dropdown list. Use a UserForm with a ComboBox for a dropdown, or a ListBox when you want multiple options visible.
How do I make an InputBox accept only numbers?
Use Application.InputBox with Type:=1. Then validate the returned number against the allowed values or range; numeric-only input does not automatically restrict the answer to a particular menu.
How can I tell whether the user clicked Cancel?
With Application.InputBox, store the result in a Variant and check whether VarType is vbBoolean; Cancel returns Boolean False. With the standard InputBox, Cancel and a blank response both return an empty string.
Can MsgBox show three custom choices?
It can show standard button sets such as Yes/No/Cancel, but it cannot replace those button captions with arbitrary labels. For three named choices, use a numbered prompt or a UserForm.
Will VBA InputBox macros work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use its VBA prompts and forms.
Related
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Return the Last Used Row Number ReliablyIn Excel VBA, the usual way to get the last row in a particular column is to start…
- Excel VBA Weekday: Syntax, Return Values, and ExamplesIn Excel VBA, use Weekday(date, firstdayofweek) to get a date’s position in the week as a number from…
- VBA Macro to Copy and Paste a Row Below the Last RowTo copy the last data row into the row immediately below it, find the last occupied cell in…
- VBA Code to Display a Graph on a UserFormUse an Excel chart on a worksheet as the graph source, export it as a JPEG, then load…
- Excel VBA: Check Whether a Sheet Is ProtectedIn Excel VBA, check Worksheet.ProtectContents to see whether a worksheet’s cells are protected. For a broader check that…
- VBA Loop Through a Dictionary: Keys, Items, and ExamplesTo loop through a VBA Dictionary, use For Each key In dict.Keys, then read each value with dict(key).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.