In VBA, the built-in InputBox function returns an empty string when the user clicks Cancel, but it also returns an empty string when the user submits a blank answer, so those actions cannot be reliably distinguished. In Excel VBA, use Application.InputBox when you need to detect Cancel separately: it returns the Boolean value False for Cancel, which you can check before using the response.
What VBA InputBox Returns
VBA offers two similarly named ways to ask for input, and their return values are the key to handling Cancel correctly. The first is the built-in InputBox function, available in VBA generally. It displays a prompt and returns what the user typed as a String. If the user clicks Cancel, it returns a zero-length string, written as "" or vbNullString.
The problem is that clicking OK without typing anything also returns a zero-length string. Once the function has returned, your code cannot tell which button the user chose. This is a limitation of the function’s return value, not an error in an If statement. Comparing the answer with "" can tell you that there is no text, but it cannot identify why.
Excel’s Application.InputBox method is different. When the user clicks Cancel, it returns the Boolean value False. When the user submits text, the result is a String—even if the typed text is the word False. That difference lets code distinguish cancellation from a blank or ordinary text response.
Detect Cancel with Application.InputBox
For an Excel macro that needs to tell Cancel apart from an empty reply, assign the result to a Variant and test its type before treating it as text. The following example requests text, reports cancellation, and separately handles a blank response:
Sub AskForName()
Dim answer As Variant
' Save a copy of the workbook before running a macro.
answer = Application.InputBox( _
Prompt:="Enter a name:", _
Title:="Name", _
Type:=2)
If VarType(answer) = vbBoolean Then
If answer = False Then
MsgBox "You cancelled the prompt."
Exit Sub
End If
End If
If Len(CStr(answer)) = 0 Then
MsgBox "You submitted a blank answer."
Exit Sub
End If
MsgBox "You entered: " & CStr(answer)
End Sub
Type:=2 tells Excel to accept text. With that setting, a normal answer is returned as text, while Cancel is returned as Boolean False. Checking VarType first is useful because a user can type the text False; that response is a String, not the Boolean value returned by Cancel. The example also uses CStr when it needs to treat the accepted result as text.
In the Visual Basic Editor, you can insert a module and paste the procedure there. On Windows, open the editor with Alt+F11; on a Mac, Option+F11 is the usual shortcut, although some keyboards require the Fn key. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
When the Built-In InputBox Is Enough
The built-in function remains appropriate when an empty answer and Cancel should have the same outcome. For example, a simple prompt might treat both as “no value supplied” and exit without changing the worksheet. In that case, checking whether the returned string has any characters is straightforward:
Sub AskForComment()
Dim answer As String
' Save a copy of the workbook before running a macro.
answer = InputBox("Enter an optional comment:", "Comment")
If Len(answer) = 0 Then
MsgBox "No comment was supplied."
Exit Sub
End If
MsgBox "Comment: " & answer
End Sub
This code intentionally does not claim to know whether the user clicked Cancel or clicked OK with an empty field. Both produce the same result, so the message describes the observable fact: no comment was supplied. Use this pattern when that combined behavior is acceptable, rather than adding a fragile attempt to guess which button was clicked.
If an empty value is invalid but cancellation does not need special treatment, you can also reject blank input and ask again. Consider trimming spaces first: an answer containing only spaces is not a zero-length string, but it may be blank for your purpose. A test such as Len(Trim$(answer)) = 0 treats leading and trailing ordinary spaces as insignificant. Decide deliberately whether spaces should be preserved in the actual value.
Choosing the Right InputBox Type
Application.InputBox includes a Type argument that controls the kinds of responses Excel accepts. It is useful when a prompt should collect more than plain text, but the return type and validation need to match what you request.
Type:=2requests text and is a practical choice for names, labels, and other text responses.Type:=1requests a number. Validate the returned value for the range and rules your macro requires; accepting a number does not automatically make it suitable for every calculation.Type:=4requests a logical value, such as True or False.Type:=8requests a range reference. This is for selecting worksheet cells, rather than entering a text value.
The type setting does not remove the need to handle Cancel. Excel documents Cancel as returning Boolean False, so retain the result in a Variant and check for that case before using it as the requested value. Be especially careful with a range response: a successful Type 8 response is an object reference, not a string or number. Code that handles ranges should check for cancellation first and then use object assignment appropriately; do not pass a range result through text conversion.
For prompts that collect several kinds of data, separate prompts or a UserForm may be easier to understand than trying to make one InputBox serve every purpose. A UserForm gives you control over labels, validation, and buttons, but it requires more setup than a one-line prompt.
Common Cancel-Handling Mistakes
A common mistake is assuming the built-in function provides a separate Cancel status. It does not: this check identifies an empty result, not the button pressed:
If answer = "" Then
' This does not distinguish Cancel from OK with no text.
End If
Another mistake is storing an Application.InputBox result in a String immediately. That discards the useful distinction between a Boolean cancellation result and a text answer, and can cause a type conversion problem. Keep the result in a Variant until after you check whether it is Boolean False.
Also avoid testing only the displayed characters of the response. The text False is not the same value as Boolean False. The type check in the earlier example makes the distinction explicit and avoids accidentally treating a user’s legitimate text as cancellation.
- Do not use
answer = ""as proof that the built-in InputBox was cancelled. - Do not declare the Application.InputBox result as String if you need its Cancel signal.
- Do not assume that a nonblank answer is valid for your task; check its content and range as needed.
- Do not expect Ctrl+Z to reverse changes made by a macro. Test code on a copy of the workbook.
Validating Answers After Cancel Is Ruled Out
Detecting Cancel only answers whether the user abandoned the prompt. It does not establish that an accepted response is useful. A text field might contain spaces, an invalid code, or a value outside the allowed choices. Keep cancellation handling and input validation as separate decisions so each has a clear purpose.
For example, a macro that asks for a department name could handle the outcomes in this order: first detect Cancel, then reject an empty or whitespace-only response, then check whether the text matches an allowed department, and only then make a workbook change. That order prevents a canceled prompt from being mistaken for an invalid entry and avoids changing cells before the input has passed validation.
If the prompt is part of a larger workflow, decide what Cancel means for the rest of that workflow. It might stop the current procedure, skip one optional field, or return control to a calling procedure. Use Exit Sub when stopping that particular Sub is intended; in a Function, use Exit Function and return a suitable value. A cancellation path should not silently continue into code that assumes a valid answer exists.
Alternatives for More Complex Prompts
InputBox dialogs are convenient for a quick, single response, but they provide limited control over layout and interaction. A UserForm can provide separate OK and Cancel buttons, clearer instructions, dropdown lists, and validation before the form closes. That can be worthwhile when users must provide multiple fields or when incorrect input could affect important workbook data.
For selecting cells or a range, Excel’s Application.InputBox with Type:=8 is designed for range selection. For a fixed list of choices, a form with a ComboBox may be clearer than asking users to type a value exactly. The right option depends on whether the response is free text, a number, a range, or one of a defined set; the Cancel check is only one part of designing a reliable prompt.
Frequently Asked Questions
What does VBA InputBox return when Cancel is clicked?
The built-in VBA InputBox function returns a zero-length String, the same result it returns when the user submits an empty answer. Excel’s Application.InputBox returns Boolean False when Cancel is clicked.
Can I tell Cancel from OK with a blank answer using InputBox?
Not with the built-in InputBox function alone. Both actions return an empty string. Use Application.InputBox in Excel VBA if you need a distinct Cancel result.
Why should the Application.InputBox result be a Variant?
Its return can be Boolean False on cancellation and a value of the requested type when the user responds. A Variant can hold either result until your code checks which one it received.
Does typing False count as clicking Cancel?
No. With Type:=2, the typed word False is a String. Cancel is Boolean False. Checking VarType(answer) = vbBoolean distinguishes the two.
How do I treat spaces as a blank answer?
For a String response, test Len(Trim$(answer)) = 0 after handling cancellation. That treats a response containing only ordinary leading or trailing spaces as blank; it does not by itself detect Cancel.
Can I undo a VBA macro with Ctrl+Z?
Generally, no. Running a VBA macro clears Excel’s ordinary undo history, so save a separate copy of the workbook before running code that may change data.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules 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 AutoFilter Method Failed? Causes and FixesYes—“AutoFilter method of Range failed” is usually a VBA runtime error caused by an invalid or unsuitable range,…
- Excel VBA Shortcut Key: Open the Editor and Run MacrosIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic for Applications (VBA) editor.…
- Excel VBA: Copy an Entire Worksheet into an Array SafelyIn Excel VBA, assign a worksheet range’s Value2 property to a Variant to copy its cell values into…
- Excel VBA: Delete a Row from a TableTo delete a data row from an Excel table with VBA, use the table’s ListRows collection: for example,…
- VBA MsgBox Line Breaks: Add a New Line to a MessageTo start a new line in a VBA MsgBox, join the text with vbCrLf: use MsgBox "First line"…
- Excel VBA Functions: Create and Use a Custom FunctionTo create a VBA function in Excel for Microsoft 365 on Windows, press Alt+F11, choose Insert > Module…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.