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:
- Declare a variable that can hold the prompt result.
- Display the prompt and assign its return value to the variable.
- Identify cancellation using the correct rule for that prompt.
- Use
Exit Subbefore 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:=2when 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
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Add Worksheet After the Last SheetTo add a worksheet after the last sheet tab in Excel, use Worksheets.Add with the last item in…
- Excel Macro Not Working? Find and Fix Common CausesIf an Excel macro is not working, first confirm that the workbook is saved in a macro-enabled format,…
- Macro Recorder for Mac OS X: Best Options and SetupFor a macro recorder on Mac OS X, use Automator’s “Watch Me Do” recording feature if your version…
- Excel VBA: Protect a Sheet and Manage Permissions SafelyYes. In Excel VBA, protect a worksheet with its Protect method, for example Worksheets("Entry").Protect Password:="demo-pass". Add options such…
- VBA MsgBox Style: Choose Buttons, Icons, and DefaultsIn VBA, a MsgBox style is the combination of buttons, an icon, the default button, and other display…
- Excel VBA UserForm: How It Works and When to Use OneAn Excel VBA UserForm is a custom window for collecting information or giving users a guided way to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.