In VBA, the standard InputBox function returns an empty string when the user clicks either OK with no text or Cancel, so those actions cannot be distinguished from its result alone. Excel’s Application.InputBox method can distinguish them: Cancel returns Boolean False, while OK with no text returns an empty string when you request text input.
What VBA InputBox Does
An input box is a small dialog that asks someone using a macro to enter a value. It is useful for a quick prompt, such as requesting a project name, a search term, or a number of rows to process. It is not a worksheet cell or a permanent form: the dialog appears while the code runs, and VBA receives the response after the user acts.
The phrase “VBA InputBox” can refer to two related but different things: the InputBox function provided by VBA, and the InputBox method provided by Excel’s Application object. Both show a dialog with OK and Cancel buttons, but their return values differ. That distinction matters when a macro needs to stop on Cancel without treating an intentionally blank answer as cancellation.
For a prompt where any blank response can simply mean “do nothing,” the standard function may be enough. If your code must tell Cancel apart from OK with an empty answer, use Application.InputBox and inspect the returned value’s type.
The Difference Between the Two Input Boxes
The VBA InputBox function returns a String. When the user clicks OK, the entered text is returned; if the user clicks Cancel, the function returns a zero-length string. Clicking OK without entering anything also returns a zero-length string. VBA therefore gives your code the same result for two different user actions.
Excel’s Application.InputBox method returns a Variant and has a Type argument that controls what kind of input the dialog accepts. For text, specify Type:=2. With that setting, the method returns the entered text as a string, including an empty string if the user clicks OK without typing. If the user clicks Cancel, it returns the Boolean value False.
The method can also request other kinds of input, including a number or a worksheet range. That flexibility is useful, but it also means that code should handle the return type deliberately. For a simple text prompt, Type 2 keeps the result predictable and makes the Cancel-versus-blank distinction straightforward.
Which Version Should You Choose?
Choose the standard function if you do not need to distinguish Cancel from an empty answer. For example, a macro could interpret an empty result as “skip this optional filter,” regardless of which button was clicked. The function is concise, but its limitation is inherent in its String return value; checking the string more carefully cannot recover information that was not returned.
Choose Application.InputBox when the difference matters. A user might cancel a process because they changed their mind, while clicking OK on a blank field might be an input error that should prompt a warning. Treating those cases separately makes the macro’s behavior clearer and can prevent it from continuing with missing information.
- Use
InputBox: a blank result and cancellation have the same meaning for your task. - Use
Application.InputBoxwith Type 2: you need to identify Cancel separately from accepted blank text. - Use a UserForm: the prompt needs several fields, custom instructions, or more control over buttons and validation.
A Safe Text Prompt That Detects Cancel
This example requests text, exits quietly if the user cancels, and treats an accepted blank or space-only answer as missing information. It uses a Variant because the method can return either a string or a Boolean. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.
Sub AskForDepartment()
Dim response As Variant
Dim answerText As String
' Request text so the response is a string or the Cancel value False.
response = Application.InputBox( _
Prompt:="Enter a department name:", _
Title:="Department", _
Type:=2)
' Cancel returns Boolean False; a blank accepted answer is a string.
If VarType(response) = vbBoolean Then
Exit Sub
End If
answerText = CStr(response)
' Treat an empty or space-only answer as missing information.
If Len(Trim$(answerText)) = 0 Then
MsgBox "No department was entered.", vbInformation
Exit Sub
End If
MsgBox "You entered: " & answerText, vbInformation
End Sub
The type check is important. Avoid relying on a simple comparison such as If response = False Then when the result could also be text or another kind of value: comparisons can involve type coercion, and a text response such as “False” is not the same as the Boolean cancellation value. Checking VarType first makes the intended test explicit. With Type 2, a Boolean result signals Cancel; a string result represents an accepted answer.
Handling Blank Answers and Validation
Cancel and blank are separate questions once you use the method: first ask whether the user cancelled, then decide what your macro should do with the accepted text. An empty string may be valid for an optional prompt, but it should usually be rejected when a value is required. Keeping those decisions separate makes the code easier to read and change.
For required text, checking Len(Trim$(answerText)) is a common way to reject both an empty string and a response made only of ordinary spaces. If leading or trailing spaces are meaningful in your particular data, do not trim them automatically; define the rule based on what the input represents. For a code, identifier, or file name, validation may also need to check permitted characters or length before the macro uses the value.
If you request a number using Application.InputBox, choose an appropriate Type and still consider the rules of the task. A value can be a valid number but outside the allowed range. For instance, a macro that expects a row count may need to reject zero, negative values, or fractions. A dialog’s input type helps constrain what can be entered, but it does not replace checks for your own requirements.
Common Mistakes and Misconceptions
- Assuming the standard function has a separate Cancel result: it does not. Cancel and OK with no text both produce an empty string.
- Testing for a blank before testing for Cancel: with
Application.InputBox, check the return type first. Otherwise, the code may attempt to treat the Boolean cancellation value as text. - Leaving out the Type argument: the method can accept different kinds of input depending on its Type setting. Specify
Type:=2when the prompt is intended to collect text. - Assuming a prompt guarantees valid data: a text dialog does not establish that a name exists, a path is usable, or an entry follows a business rule. Validate the returned text before acting on it.
- Expecting Ctrl+Z to reverse a macro: Excel’s Undo command generally cannot undo changes made by VBA. Save a backup before running code that edits data.
Another misconception is that the Cancel button always means an error. It is usually better understood as a user decision to stop or defer the requested action. In many macros the right response is simply to exit without changing anything, rather than displaying an alarming error message.
When a UserForm Is a Better Alternative
A one-line prompt is convenient, but it becomes awkward when users need to enter several related values, see examples, choose from a list, or correct mistakes before submitting. A VBA UserForm can provide text boxes, labels, list controls, and explicit buttons such as Submit and Cancel. Your code can then track which button the user chose and validate fields before accepting the form.
A UserForm takes more setup than an InputBox and is not necessary for every small macro. Consider one when users repeatedly make avoidable entry mistakes, when a prompt needs detailed instructions, or when the dialog is part of a larger workflow. For a single optional text value, Application.InputBox is often the simpler solution.
Excel Platform and Macro Notes
These examples are for desktop Excel with VBA. Excel for the web can open and edit many macro-enabled workbooks, but it cannot run VBA macros; use the desktop app to run this code. Excel for Mac also supports VBA, although the location of some settings and keyboard shortcuts can differ from Windows.
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic if the Developer tab is visible. On Mac, the Visual Basic Editor is available from the Developer tab; the keyboard equivalent is commonly Option+F11, though some keyboards require Fn as well. To add the code, choose Insert > Module in the editor. If the Developer tab is hidden, enable it in Excel’s ribbon settings.
Macros may be blocked by Excel’s security settings or by an organization’s policies. Only run VBA from a workbook and source you trust. Saving as an Excel Macro-Enabled Workbook (.xlsm) preserves VBA code; saving as a standard .xlsx file does not retain macros.
Frequently Asked Questions
Does VBA InputBox return blank when I click Cancel?
The built-in VBA InputBox function returns an empty string for Cancel, and it returns the same empty string when the user clicks OK without typing. Excel’s Application.InputBox returns Boolean False for Cancel.
How do I tell Cancel from OK with no text in VBA?
Use Application.InputBox with Type:=2, store the result in a Variant, and test whether VarType(result) = vbBoolean. A Boolean result indicates Cancel; a string result can be empty if the user accepted a blank answer.
Can I use the normal InputBox function and detect Cancel?
Not from the function’s returned value alone. Since Cancel and OK with no text both return a zero-length string, use the Excel method or a custom UserForm if your code must distinguish the actions.
What does Type:=2 mean in Application.InputBox?
It tells Excel’s method to accept text. This is useful for a text prompt because the accepted response is a string, while Cancel is returned as Boolean False.
Can an InputBox macro run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the VBA code.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Check If Worksheet Exists: Code and ExamplesTo check whether a worksheet exists in VBA, try to set a Worksheet variable from the target workbook’s…
- Delete Table Row in VBA: Excel Examples and TipsTo delete a row from an Excel table with VBA, use the table’s ListRows collection: identify the table…
- Outlook Macros: How to Enable Them Safely in Classic OutlookTo enable VBA macros in classic Outlook for Windows, open File > Options > Trust Center > Trust…
- VBA Include Quotes in a String: Examples and FixesTo include a double quotation mark inside a VBA string, double it in the string literal: write message…
- Excel VBA: Insert the Current Date and Time in a CellTo insert a fixed date-and-time timestamp with Excel VBA, assign the VBA function Now to a cell, then…
- VBA Copy PasteSpecial: Values, Formats, and MoreIn Excel VBA, copy a range and call PasteSpecial on the destination to paste only values, formats, formulas,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.