VBA InputBox Cancel: Exit a Sub Safely

VBA & macros · Updated November 18, 2026

Yes. To stop a VBA procedure when the user cancels an input prompt, test the prompt’s return value and then use Exit Sub. For a reliable distinction between Cancel and an empty response, use Excel’s Application.InputBox; the older VBA InputBox function returns an empty string for both.

Why the InputBox type matters

VBA offers two commonly used prompts with similar names, but their Cancel results differ. The built-in InputBox function returns a String. If the user clicks Cancel, it returns a zero-length string. It also returns a zero-length string if the user submits the prompt without typing anything. Therefore, checking whether the result is empty can stop the macro, but it cannot tell you which of those two actions occurred.

Excel’s Application.InputBox method returns a Variant. When the user clicks Cancel, it returns the Boolean value False. When configured for text entry, a response typed as the word “False” is still text, not the Boolean value. That difference lets your macro identify cancellation explicitly.

This distinction matters when an empty response has meaning. For example, a blank might be acceptable when collecting an optional note, while Cancel should stop the operation. If blank input is not acceptable, either prompt can be made to exit on an empty response, but the built-in function still cannot reveal whether the user clicked Cancel or submitted a blank.

Exit a Sub when Application.InputBox is canceled

For Excel VBA, set Type:=2 to request text, save the result in a Variant, and check whether the returned value is a Boolean before using it as text. The Boolean check avoids confusing cancellation with a text response. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Sub AskForDepartment()
    Dim answer As Variant
    Dim department As String

    ' Type 2 requests a text response.
    answer = Application.InputBox( _
        Prompt:="Enter a department name:", _
        Title:="Department", _
        Type:=2)

    ' Cancel returns the Boolean value False.
    If VarType(answer) = vbBoolean Then
        If answer = False Then Exit Sub
    End If

    department = Trim$(CStr(answer))
    If Len(department) = 0 Then
        MsgBox "Enter a department name before continuing."
        Exit Sub
    End If

    MsgBox "Continuing with: " & department
End Sub

The first Exit Sub ends this procedure immediately, so the code that reads and uses the department is skipped. The second one handles a different case: the user submitted text that is empty or consists only of ordinary spaces. If blank input is permitted in your workflow, remove that second check and decide how the rest of the macro should handle an empty string.

Using the built-in InputBox function

If you do not need to distinguish Cancel from an intentionally blank answer, the built-in function can be checked with Len. This is concise, but it treats both actions identically. Make that behavior clear in the prompt so users understand that leaving the field empty will stop the procedure.

Sub AskForComment()
    Dim commentText As String

    ' The built-in InputBox returns a String.
    commentText = InputBox( _
        Prompt:="Enter a comment. Leave it blank to stop:", _
        Title:="Comment")

    ' A blank submission and Cancel both produce an empty string.
    If Len(commentText) = 0 Then Exit Sub

    MsgBox "Comment received: " & commentText
End Sub

Use Len(commentText) = 0 when only a truly empty string should stop the code. If spaces should count as blank too, test Len(Trim$(commentText)) = 0. Trimming changes the value by removing leading and trailing ordinary spaces; if those spaces are meaningful in your data, do not trim it automatically.

Where to put Exit Sub

Put the cancellation check directly after the prompt and before any code that depends on the answer. This makes the control flow easy to read: ask, check, then proceed. If the macro has already changed cells, created files, or performed other work before displaying the prompt, Exit Sub will not reverse those actions. It only stops execution from that point onward.

For a straightforward macro, the pattern is:

  1. Declare a variable that can hold the prompt result.
  2. Display the prompt and assign its return value to the variable.
  3. Identify cancellation using the correct rule for that prompt.
  4. Use Exit Sub before the procedure performs the next operation.

In the Visual Basic Editor, the procedure must be a Sub for Exit Sub to be valid. A Function procedure needs Exit Function instead. In a worksheet event or button-click procedure, the same rule applies: exiting stops that event procedure, not Excel itself.

What Exit Sub does—and does not do

Exit Sub ends only the current Sub procedure and returns control to its caller, if there is one. It does not close the workbook, stop Excel, or automatically cancel every procedure that may have called the current one. For example, if one macro calls another and the second macro exits after a canceled prompt, the first macro can continue with its next statement.

If cancellation needs to affect a calling procedure, return a status rather than relying on Exit Sub alone. A Boolean-returning Function is one option: it can return False when the user cancels, and the caller can decide whether to exit. This is useful when input collection is a reusable step shared by several macros, because it keeps the cancellation decision visible to the code that controls the larger task.

Also distinguish a normal cancellation from an error. A Cancel click is a return value, not an error that requires On Error Resume Next. Suppressing errors is unnecessary for the standard Cancel behavior and can hide genuine problems elsewhere in the procedure.

Application.InputBox options and response types

The optional Type argument controls what kind of response Application.InputBox accepts. For example, Type:=2 requests text and Type:=1 requests a number. The return value is a Variant, so code should treat it according to the chosen type. The Cancel result remains Boolean False, which is why checking VarType before converting a text response is a useful pattern.

For a text prompt, the key points are:

  • Use Type:=2 when the response should be text.
  • Store the response as a Variant until you have checked for cancellation.
  • Convert it to a String only after the cancellation check.
  • Decide separately whether an empty or whitespace-only answer is allowed.

Other types need type-appropriate handling. In particular, requesting a range is different from requesting text: Excel returns a Range object when the user selects cells. Do not copy a text-response pattern blindly into a range prompt. If your macro accepts multiple kinds of responses, validate the actual return type before using it.

Common mistakes and how to avoid them

  • Checking only for an empty string with Application.InputBox: This may stop on a blank response, but it does not express the explicit Cancel check. For a text prompt, check for the Boolean result first, then handle blank text separately.
  • Using the Boolean check with the built-in InputBox: The built-in function returns a String, including an empty string on Cancel. It does not provide the same Boolean Cancel signal.
  • Declaring the Application.InputBox result as String: A String variable cannot preserve the returned Variant Boolean in the same way. Keep the result in a Variant until you have checked it.
  • Putting the check after an operation: If the macro writes to a cell before checking cancellation, that write has already happened. Check immediately after the prompt if nothing should happen on Cancel.
  • Expecting the whole macro chain to stop: Exiting a called Sub returns to its caller. The caller must check a status or otherwise decide what to do next.
  • Using On Error Resume Next to handle Cancel: Cancel is a normal return condition. Error suppression is not a substitute for checking the result.

When to choose each prompt

Choose the built-in InputBox when a simple text question is enough and treating Cancel the same as blank is acceptable. It is easy to use, but offers limited control over the response. Choose Application.InputBox in Excel when you need to distinguish Cancel from a submitted value, or when the user should provide a specific response type such as text or a number.

Neither prompt is a replacement for a custom UserForm. If you need several fields, clearer validation, buttons with different meanings, or a more guided workflow, a UserForm gives you more control over the interface. For a single question, a standard input box is usually simpler and easier to maintain. Whichever approach you use, describe what Cancel and a blank answer mean before the user reaches the prompt.

Frequently Asked Questions

Does VBA InputBox return an empty string when I click Cancel?
The built-in VBA InputBox function returns a zero-length string when canceled, and it also does so when the user submits an empty response. Excel’s Application.InputBox instead returns Boolean False on Cancel.

How do I exit a Sub if the user clicks Cancel?
With Application.InputBox, store the result in a Variant, check whether VarType(result) = vbBoolean, confirm it is False, and then use Exit Sub. For the built-in function, an empty-string check stops on both Cancel and blank input.

Can I tell Cancel from a blank answer with InputBox?
Not with the built-in InputBox function alone, because both produce an empty string. Use Excel’s Application.InputBox for an explicit Cancel value, then check blank text as a separate condition.

Does Exit Sub stop the macro that called this Sub?
No. It ends only the current Sub and returns to the caller. If the caller must stop too, have the called procedure return a status, such as a Boolean from a Function, and test that status in the caller.

Should I use On Error Resume Next for a canceled prompt?
No. Cancel is represented by a return value, not by a runtime error in these standard prompt patterns. Check the result directly so real errors are not silently ignored.

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.