Yes. To stop the current Excel VBA procedure when a user cancels an input box, test the returned value and use Exit Sub. With the built-in VBA InputBox, Cancel and submitting a blank response both return an empty string; use Application.InputBox when you need to tell Cancel apart from blank text.
What Cancel returns in VBA
Excel VBA offers two commonly used input-box methods, and their Cancel behavior is different. The built-in InputBox function returns text. If the user clicks Cancel, it returns a zero-length string, written as "". Submitting the dialog with no text also returns "", so the result alone cannot tell you which action occurred.
Application.InputBox, a method of Excel’s Application object, returns a Variant. When the user clicks Cancel, it returns the Boolean value False. That gives your code a distinct cancellation result, provided you check the Variant’s type before interpreting it. This distinction matters when blank input is allowed, or when a typed response could look like a Boolean value.
Exit Sub ends the procedure currently running. It does not close Excel, stop unrelated procedures, or undo changes the macro has already made. Put the cancellation check before any work that should not happen unless the user confirms or supplies input.
Exit a Sub after the built-in InputBox
If your macro treats blank input as cancellation, the built-in function is straightforward. This pattern is appropriate when there is no need to distinguish a deliberate blank response from clicking Cancel.
Sub AskForName()
Dim response As String
' Ask for a value; blank and Cancel both return an empty string.
response = InputBox("Enter a name:", "Name")
If Len(response) = 0 Then
Exit Sub
End If
MsgBox "You entered: " & response
End Sub
Len(response) = 0 checks for an empty string. It does not reject a response made only of spaces: for example, three spaces have a length of three. If spaces should count as blank in your application, trim the response before testing it, as shown later. Remember that after trimming, a spaces-only response and Cancel still share the same outcome with this function.
Distinguish Cancel with Application.InputBox
Use Application.InputBox when Cancel must be handled separately from an empty answer. Setting Type:=2 requests text. Store the result in a Variant, because the result is normally text but Cancel returns a Boolean.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a later part of a procedure may change cells before you decide to revise the code.
Sub AskForOptionalText()
Dim answer As Variant
' Type 2 requests text; Cancel returns Boolean False.
answer = Application.InputBox( _
Prompt:="Enter a note, or leave it blank:", _
Title:="Optional note", _
Type:=2)
' Check the type first so text is not mistaken for the Cancel value.
If VarType(answer) = vbBoolean Then
If answer = False Then
Exit Sub
End If
End If
MsgBox "You entered: [" & CStr(answer) & "]"
End Sub
The nested checks are intentional. VBA’s And operator does not short-circuit: both sides of an And expression are evaluated. Separating the type test from the comparison avoids relying on a Boolean comparison being safe for every possible text response. The brackets in the message make an empty answer visible as [].
Choose the right input-box method
The choice depends on what your macro needs to know, not simply on which method is newer. The built-in function is convenient for simple prompts where an empty result should end the procedure. Excel’s method gives you more control over the response type and a distinct Cancel value.
- Use
InputBoxfor a quick text prompt when blank and Cancel can have the same meaning. - Use
Application.InputBoxwithType:=2when blank text is valid but Cancel should exit. - Use
Application.InputBoxwith a suitable type when you want Excel to request a number, cell reference, or another supported kind of entry. - Use a UserForm when the interaction needs several fields, custom buttons, or more detailed validation than a single dialog can comfortably provide.
The optional Type argument controls what the user can enter. For example, Type:=1 requests a number and Type:=2 requests text. Excel supports other types too, including a range reference, but choose a type that matches the task and confirm the returned value is suitable before using it. A typed input box helps constrain entry; it does not replace checks for business rules such as an allowed range or a required value.
Validate text without confusing blank and Cancel
When empty text is permitted, first identify cancellation, then apply any rules to the entered text. The following example accepts a blank response but treats a response containing only spaces as blank. It still allows the user to cancel separately.
Sub AskForSearchText()
Dim answer As Variant
Dim searchText As String
' Request text so a blank answer remains distinct from Cancel.
answer = Application.InputBox( _
Prompt:="Enter search text; blank is allowed:", _
Title:="Search", _
Type:=2)
' Stop only when the result is the Boolean Cancel value.
If VarType(answer) = vbBoolean Then
If answer = False Then Exit Sub
End If
searchText = Trim$(CStr(answer))
If Len(searchText) = 0 Then
MsgBox "You submitted a blank search."
Exit Sub
End If
MsgBox "Searching for: " & searchText
End Sub
In this example, a blank submission follows a different path from Cancel: it displays a message and exits, while Cancel exits without displaying that message. If you want to preserve leading or trailing spaces, do not trim the response; trimming is a policy choice, not a requirement of InputBox.
Use the response before changing the workbook
A reliable macro collects and checks required input before it edits cells, deletes records, or starts a longer process. This makes the Cancel route easier to reason about and avoids leaving a workbook partly changed just because the user backed out of a prompt.
For instance, suppose a macro asks for a worksheet name and then clears a range on that sheet. Check for Cancel first, then check whether the name is blank, and then verify that the worksheet exists before clearing anything. The Cancel check does not establish that a requested sheet exists, and a nonblank answer is not automatically a valid sheet name.
For procedures that do make changes before a later prompt, consider whether the work should be postponed until all required answers have been collected. If postponing is impractical, plan an explicit cleanup or recovery path. Exit Sub is an immediate exit, not a transaction: it does not roll back earlier cell changes, restore deleted data, or run code placed after the exit statement.
Common mistakes and how to avoid them
- Assuming the two methods return the same thing: The built-in
InputBoxreturns an empty string for both Cancel and blank submission.Application.InputBoxreturns BooleanFalsefor Cancel. - Using a String variable for the Excel method: Keep the result in a Variant until you have checked whether it is the Boolean Cancel value. Converting too early can discard the distinction you need.
- Checking only for an empty string with
Application.InputBox: Cancel is not the same as an empty text response there. Test the result’s type and then its Boolean value. - Putting cancellation detection after workbook changes: Move the prompt and validation ahead of operations that should not occur if the user cancels.
- Expecting
Exit Subto stop everything: It exits only the current Sub. If the procedure was called by another macro, the calling procedure may continue after the call returns. - Expecting Ctrl+Z to reverse a macro: Excel generally clears the undo history when a VBA macro runs. Save a backup before running code that changes workbook contents.
Where to put and run the VBA code
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste a procedure into the module, and run it from the editor with the insertion point inside the procedure by pressing F5. You can also run a public macro from Excel’s Macro dialog, opened with Alt+F8.
On a Mac, use the Visual Basic Editor from the Developer tab or Excel’s Tools > Macro menu; keyboard shortcuts can vary with the keyboard and macOS function-key settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application to work with these procedures. If Excel blocks a macro, review the workbook’s security settings and only enable macros from sources you trust.
Frequently Asked Questions
Does Cancel in VBA InputBox return False?
The built-in VBA InputBox returns an empty string, not Boolean False. Application.InputBox returns Boolean False when the user clicks Cancel.
Can I tell Cancel from a blank answer with InputBox?
Not from the built-in InputBox return value: both actions produce an empty string. Use Application.InputBox with Type:=2 if the distinction matters.
What does Exit Sub do in Excel VBA?
It ends the currently running Sub procedure immediately. It does not close the workbook or Excel, undo changes, or necessarily stop a caller from continuing.
Should I use Exit Sub or End?
Use Exit Sub for a normal early exit from one procedure. End stops VBA execution abruptly across the project and can prevent cleanup code from running, so it is not a substitute for handling a canceled prompt.
Can Application.InputBox accept a blank text response?
Yes. With Type:=2, an empty text response can be handled separately from Cancel by checking whether the returned Variant is Boolean before treating it as text.
Related
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Filter: How to Automate Data FilteringTo filter a worksheet with a macro, use VBA’s AutoFilter method on a range that includes its header…
- Excel VBA Break Loop: Keyboard Shortcuts and FixesTo interrupt a running Excel VBA loop on Windows, press Ctrl+Break (sometimes labeled Pause/Break); on a Mac, press…
- Excel VBA: Check If a String Contains a Substring in a CellIn Excel VBA, use InStr to check whether one string contains another: it returns a position greater than…
- Excel VBA: Save a Worksheet as a New File SafelyTo save one Excel worksheet as a separate file with VBA, copy it to a new workbook with…
- Excel VBA: Save a Workbook as a New File with SaveAsTo save an Excel workbook as a new file with VBA, use the workbook’s SaveAs method and supply…
- Can You Have Macros in Google Sheets? How They WorkYes. Google Sheets supports macros: you can record a sequence of actions and replay it later, or edit…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.