Excel VBA InputBox Cancel: Exit a Sub Safely

VBA & macros · Updated November 21, 2026

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 InputBox for a quick text prompt when blank and Cancel can have the same meaning.
  • Use Application.InputBox with Type:=2 when blank text is valid but Cancel should exit.
  • Use Application.InputBox with 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 InputBox returns an empty string for both Cancel and blank submission. Application.InputBox returns Boolean False for 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 Sub to 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.