In Excel VBA, the built-in InputBox function returns an empty string when the user clicks Cancel, just as it does when the user clicks OK without entering anything. To distinguish cancellation from a submitted blank answer, use Excel’s Application.InputBox method and check whether its returned value is the Boolean value False.
What Cancel means in VBA
VBA has two similarly named ways to show an input dialog, and their different return values are the key to handling Cancel. The built-in InputBox function returns text. If the user clicks Cancel, it returns a zero-length string, represented as "". If the user leaves the field empty and clicks OK, the result is also a zero-length string. The function does not tell your macro which of those two actions occurred.
That behavior is fine when either action should mean “no answer.” For example, a macro might simply stop if the user supplies no project name, whether they canceled or left the field blank. It is a problem when those actions should lead to different outcomes, such as treating a blank answer as an intentional value while letting Cancel abandon the operation.
Cancel does not automatically close a procedure or undo earlier steps in a macro. Your code must examine the return value and decide what to do next. That may mean exiting the procedure, asking again, or continuing with a blank value.
InputBox function and Application.InputBox compared
The plain InputBox is a VBA function that accepts a prompt and optional dialog settings, then returns a string. It is convenient for a short text question, but its string result makes Cancel indistinguishable from OK with an empty field.
Application.InputBox is a method exposed by Excel. It also displays a dialog, but it can constrain the kind of input and returns a Variant. When the user clicks Cancel, the method returns Boolean False. When the user submits text with Type:=2, it returns that text, including an empty string if an empty answer is accepted. This gives your code a separate cancellation signal.
Specify Type:=2 when you want text. If you omit the type, Application.InputBox uses its formula-oriented default, which may not fit a simple text prompt. Other type settings are available for inputs such as numbers and ranges; the return type and validation behavior depend on the type you request.
Detect Cancel with Application.InputBox
Store the result in a Variant, then check its type before treating it as text. Checking the type matters: a user could legitimately type the word False, and that submitted answer is a string, not the Boolean value returned for cancellation. The following example distinguishes Cancel, a blank submission, and ordinary text.
Sub AskForProjectName()
Dim answer As Variant
' Save a copy of the workbook before running a macro; Ctrl+Z cannot undo macros.
answer = Application.InputBox( _
Prompt:="Enter a project name:", _
Title:="Project name", _
Type:=2)
If VarType(answer) = vbBoolean Then
If answer = False Then
MsgBox "You canceled 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 "Project name: " & CStr(answer)
End Sub
The nested test is deliberate. VarType confirms that the result is Boolean before the code compares it with False. After the cancellation branch exits, the remaining result can be converted to text and checked. If your macro should treat both Cancel and blank as the same choice, you can combine those paths after checking for cancellation, or use the simpler built-in function when the distinction is not needed.
Decide what a blank answer should do
Detecting Cancel is only one part of the design. Decide what an empty answer means in your particular workflow. For a required value, a blank answer may call for another prompt. For an optional note, it may be a valid submission. For a command that could change many cells, Cancel should usually leave the operation untouched.
With Application.InputBox and Type:=2, you can check for an empty string separately after the Boolean cancellation check. If you want to reject answers consisting only of ordinary leading or trailing spaces, use Trim$ before checking their length. Do not trim automatically if spaces are meaningful in the data you are collecting. Also consider whether the user should be allowed to submit a blank value or should instead be prompted again.
A useful interaction pattern is to collect and validate the answer before changing the workbook. If the user cancels or the answer fails validation, exit before writing data. That keeps the dialog’s decision separate from the macro’s changes and makes the outcome easier to understand.
Validate the answer without confusing it with Cancel
Input validation comes after cancellation handling. For a text response, check that the value is present and meets the needs of your task—for example, that a required identifier is not blank. If the answer must match a pattern or one of a known set of choices, validate it explicitly rather than assuming that displaying a prompt guarantees a suitable answer.
For numeric input, Application.InputBox with Type:=1 can restrict the dialog to numbers. Still, handle the cancel result before using the value in arithmetic. If you use the plain VBA InputBox, its result is text, so a numeric-looking answer needs validation and conversion. Be aware that decimal and thousands separators can depend on the user’s regional settings; avoid assuming every computer uses the same number format.
Do not rely on a comparison such as answer = False as your only test when the value could also be text or a number. VBA can coerce values during comparisons, making a type check more dependable. Keeping the result as a Variant until you have checked for the Boolean cancellation value avoids converting the cancellation signal prematurely.
Common mistakes and confusing results
- Using the plain function and expecting a separate Cancel value:
InputBoxreturns an empty string for both Cancel and an empty OK submission. UseApplication.InputBoxif you must tell them apart. - Checking only whether the answer is empty: With the plain function that detects both cases, not just Cancel. With the application method, check the Boolean result first, then test for an empty text response.
- Comparing text directly with False: A user might enter the text False. Check
VarType(answer) = vbBooleanbefore interpreting Boolean False as cancellation. - Converting the result too early: A cancellation result is Boolean, not a text answer. Avoid applying string operations or numeric conversions until after the cancellation check.
- Expecting Cancel to reverse workbook edits: It only returns a result from the dialog. It does not roll back changes already made by your macro.
Another small source of confusion is whitespace. A string containing spaces is not an empty string, so a basic length check will accept it. Trim the value only if your data rules say surrounding spaces should not count. If the prompt is important, tell users whether leaving it empty is permitted and what Cancel will do.
Using Application.InputBox to select a range
Application.InputBox can also prompt a user to select cells by using Type:=8. In that case, the intended result is a Range object rather than text. Cancellation needs object-aware handling: assigning the Cancel result directly to a range variable can raise a type mismatch. One approach is to suppress errors only around that assignment and then check whether the range variable remains Nothing.
Sub AskForRange()
Dim selectedRange As Range
' Save a copy of the workbook before running a macro; Ctrl+Z cannot undo macros.
On Error Resume Next
Set selectedRange = Application.InputBox( _
Prompt:="Select a range of cells:", _
Title:="Choose a range", _
Type:=8)
On Error GoTo 0
If selectedRange Is Nothing Then
MsgBox "No range was selected."
Exit Sub
End If
MsgBox "Selected range: " & selectedRange.Address
End Sub
Keep On Error Resume Next narrowly scoped, as in this example. Leaving it active for the rest of a macro can hide unrelated errors and make failures harder to diagnose. If you use a different input type, check the returned value in a way that matches that type rather than copying a text-input test without considering the result.
Excel version and macro considerations
These VBA examples are for desktop Excel, including Microsoft 365 for Windows. Excel for Mac also supports VBA and Application.InputBox, though dialog appearance and some surrounding Excel features can differ. Excel for the web does not run VBA macros; a workbook that depends on this code must be used in the desktop application to execute it.
Save a workbook containing VBA in a macro-enabled format such as .xlsm, and follow your organization’s macro security rules. A macro’s changes generally cannot be reversed with Ctrl+Z, so save a separate copy before running code that modifies important data. The examples above only display messages after prompting, but the same precaution is important when adapting them to write to cells or change workbook contents.
Frequently Asked Questions
Does VBA InputBox return False when Cancel is clicked?
No. The built-in VBA InputBox function returns a zero-length string for Cancel. Excel’s Application.InputBox method returns Boolean False on Cancel, so use that method when you need to identify cancellation separately.
How do I tell Cancel from a blank answer in Excel VBA?
Use Application.InputBox with Type:=2, store the result in a Variant, and check whether VarType(result) = vbBoolean before checking whether the submitted text is empty.
Why should I use VarType instead of checking answer = False?
A user can submit the text False, and other result types can also make loose comparisons confusing. Checking that the result is Boolean first distinguishes the cancellation signal from ordinary text.
Does clicking Cancel stop the whole macro?
No. It returns a value from the prompt. Your code must respond—for example, by displaying a message and using Exit Sub—and should do so before making changes that should not happen after cancellation.
Can Excel for the web run an InputBox macro?
No. Excel for the web does not execute VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run VBA.
Related
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA to Send Email in Outlook 365: Setup and CodeYes. In Excel for Microsoft 365 on Windows, VBA can create and send an email through the classic…
- VBA Convert String to Date: Safe Parsing in Excel MacrosIn VBA, use CDate to convert a string that VBA recognizes as a date, or DateValue when you…
- VBA AutoFilter Off: Remove Arrows or Clear FiltersTo show every row while keeping the filter controls, use VBA’s ShowAllData method when the worksheet is filtered.…
- VBA Code to Open an Excel File from SharePointIn Excel for Microsoft 365 on Windows, use VBA’s Workbooks.Open method with the file’s direct SharePoint URL, provided…
- Excel Macro to Compare Two Excel Sheets Cell by CellTo compare two worksheets in Excel for Microsoft 365 on Windows, use a VBA macro that checks the…
- VBA in Excel: Open the Editor with a Keyboard ShortcutTo open the VBA editor in Excel for Microsoft 365 on Windows, press Alt+F11. This opens the Visual…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.