To stop a VBA Sub when someone clicks Cancel, use a MsgBox that includes the Cancel button, check whether its return value is vbCancel, and then run Exit Sub. For example: If MsgBox("Continue?", vbOKCancel) = vbCancel Then Exit Sub. The check must happen before the code you want to prevent.
How MsgBox reports a Cancel click
The VBA MsgBox function displays a dialog and returns a value indicating which button the user selected. The return value is a member of the VbMsgBoxResult enumeration. When a message box includes a Cancel button and the user selects it, the function returns vbCancel. Your procedure can compare that result with the constant and decide what to do next.
In Excel for Microsoft 365 on Windows, this is ordinary VBA code, not a worksheet formula. A message box with an OK button alone cannot return vbCancel, because there is no Cancel choice. Choose a button set that actually includes Cancel, such as vbOKCancel or vbYesNoCancel, before writing a Cancel check.
Exit Sub ends the current Sub procedure immediately. It does not close Excel, stop unrelated macros, or reverse changes the procedure has already made. That makes the order of operations important: ask for confirmation before deleting, replacing, or otherwise changing data.
The basic pattern
For a two-button prompt, store or directly test the result. A named variable makes the user’s choice easier to read, especially when the procedure has more than one branch:
Option Explicit
Public Sub ConfirmAction()
Dim answer As VbMsgBoxResult
answer = MsgBox("Continue with this action?", vbOKCancel + vbQuestion, "Confirm")
If answer = vbCancel Then Exit Sub
' Put the action that requires confirmation here.
MsgBox "Continuing."
End Sub
The first argument is the prompt, the second selects the buttons and optional icon, and the third supplies the title. Here, vbOKCancel creates the two choices and vbQuestion adds an icon. The constants can be combined with + or Or. If the user chooses OK, execution passes the condition and reaches the action. If the user chooses Cancel, Exit Sub ends the procedure.
For a very short check, you can put the call directly in the condition: If MsgBox("Continue?", vbOKCancel) = vbCancel Then Exit Sub. Assigning the result to a variable is often clearer if you want to handle both choices explicitly or inspect the value while debugging.
Put the check before the risky work
A confirmation prompt only protects actions that have not happened yet. If a macro clears a range and then asks whether to continue, choosing Cancel will stop later statements but will not restore the cleared cells. Put the prompt near the start of the procedure, after any harmless setup needed to explain the choice and before the first consequential change.
This example asks before clearing a worksheet range. It uses a Cancel branch and a separate OK branch so the intended flow is visible:
Option Explicit
Public Sub ClearImportArea()
Dim answer As VbMsgBoxResult
answer = MsgBox("Clear the contents of A2:D100?", _
vbOKCancel + vbExclamation, "Confirm clearing")
If answer = vbCancel Then Exit Sub
If answer = vbOK Then
ThisWorkbook.Worksheets("Import").Range("A2:D100").ClearContents
End If
End Sub
ClearContents removes cell contents without removing the cells themselves or their formatting. The confirmation does not make this operation reversible through VBA: Excel’s undo history is generally cleared when a macro runs, so Ctrl+Z cannot be relied on to restore the range. Save a copy of the workbook before running a macro that changes data.
Choosing the right buttons and result
Use buttons that match the decision the prompt is asking the reader to make. The choice affects both the wording and the result constants your code should test. Common button groups include:
vbOKCancel: returnsvbOKorvbCancel. This suits a straightforward continue-or-stop prompt.vbYesNo: returnsvbYesorvbNo. It does not include a Cancel result, so do not test it forvbCancel.vbYesNoCancel: returnsvbYes,vbNo, orvbCancel. Use this when the user needs three distinct choices.vbRetryCancel: returnsvbRetryorvbCancel, which can suit an operation that may be attempted again.
For vbYesNoCancel, handle all three values if each choice should do something different. A No answer is not the same as Cancel: No may mean “continue without the optional step,” while Cancel often means “stop this operation.” Decide what each button means in plain language, then make the code match.
Option Explicit
Public Sub ChooseImportMode()
Dim answer As VbMsgBoxResult
answer = MsgBox("Import the new rows?", vbYesNoCancel + vbQuestion, "Import")
If answer = vbCancel Then Exit Sub
If answer = vbNo Then Exit Sub
' The remaining path represents Yes.
MsgBox "Importing rows."
End Sub
This example treats both No and Cancel as reasons not to import, but distinguishes them in the code. If No should lead to a different action, put that action in its own branch rather than letting execution fall through to the import.
Common mistakes that prevent the expected result
One frequent mistake is checking the wrong constant. With vbOKCancel, compare against vbCancel, not vbNo; the dialog has no No button. Similarly, a vbYesNo dialog can return vbYes or vbNo, but cannot return vbCancel.
Another error is putting the check after the operation. Exit Sub stops only the statements that follow it. It does not travel backward in time to undo prior lines. A third is placing Exit Sub in a Function: it is for Sub procedures. A Function needs to set its return value as appropriate and use Exit Function to leave early.
Also check that you are comparing the result of the message box, not the prompt text or an unrelated variable. This is valid: answer = MsgBox(...), followed by If answer = vbCancel Then. A variable declared as VbMsgBoxResult documents the expected kind of value and helps make the code self-explanatory. With Option Explicit, VBA will also flag undeclared variable names rather than silently treating a typo as a new variable.
Finally, remember that the Cancel option is a user-interface choice, not a guarantee that every way of dismissing a dialog has been handled identically across all circumstances. If your macro depends on a particular response, include the intended button and test the actual dialog flow in the workbook. Do not assume that an OK-only message can be cancelled into a meaningful vbCancel result.
Stopping cleanly when setup needs to be undone
A simple Exit Sub is appropriate when there is nothing to clean up. But some procedures open a file, switch application settings, or create a temporary resource before asking a later question. Exiting immediately can leave that setup unfinished. In that situation, use a clear cleanup path, or move the confirmation earlier so setup is not needed before the decision.
For example, if you turn off screen updating or change calculation settings, make sure every exit path restores the original settings. One approach is to route cancellation to a cleanup label instead of exiting immediately. Use this structure only when cleanup is genuinely required; a label is not necessary for a simple prompt.
Option Explicit
Public Sub ExampleWithCleanup()
Dim answer As VbMsgBoxResult
Dim oldScreenUpdating As Boolean
oldScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
answer = MsgBox("Continue?", vbOKCancel, "Confirm")
If answer = vbCancel Then GoTo CleanUp
' Perform the work that requires confirmation.
CleanUp:
Application.ScreenUpdating = oldScreenUpdating
End Sub
For production code with multiple possible errors, cleanup should also be considered in error-handling logic so that an unexpected failure does not leave settings changed. Keep the control flow easy to follow, and avoid adding global state or complicated error handling to a macro that only needs a prompt at the beginning.
Where to add and run the VBA code
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a public Sub into the module. Run it from the editor with the cursor inside the procedure and F5, or run it from Excel with Developer > Macros, select the macro, and choose Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
On a Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor, and the Developer tab can also provide access if enabled. The keyboard shortcut depends on the Mac keyboard and function-key settings; Option+F11 or Fn+Option+F11 may be needed. Menu access is a reliable alternative when the shortcut is intercepted by macOS or the keyboard. VBA dialogs and the core return-value pattern work in desktop Excel for Mac, though some Excel object-model features outside this example can differ by platform.
Before running a macro that edits a workbook, save a separate copy. Excel macros cannot be undone with Ctrl+Z, and a Cancel branch only stops future statements; it does not restore changes already made. In a workbook downloaded from elsewhere, do not enable macros unless you trust the source and understand what the code will do.
Frequently Asked Questions
How do I make Cancel exit a VBA Sub? Include Cancel in the MsgBox buttons, capture the return value, and test it before the work: If answer = vbCancel Then Exit Sub. The variable answer must first hold the result returned by MsgBox.
What does MsgBox return when Cancel is clicked? For a message box that includes a Cancel button, the return value is vbCancel. It is a named VBA constant representing the selected button, not the text shown in the dialog.
Can I use vbCancel with vbYesNo? No. The vbYesNo button set has Yes and No choices only. Use vbOKCancel, vbYesNoCancel, or another button set that includes Cancel if you need to detect vbCancel.
Does Exit Sub undo changes already made? No. It stops the current procedure at that point but does not reverse previous edits, restore Excel’s undo history, or close other procedures. Ask before making changes, and save a copy before running a modifying macro.
Can I use Exit Sub inside a VBA Function? No. Use Exit Function to leave a Function. Set the function’s return value on the appropriate paths if the calling code expects one.
Related
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA IsError: Check for Worksheet Errors in CodeIn Excel VBA, IsError(expression) returns True when an expression evaluates to an error value and False otherwise. Use…
- VBA Array Set: Assigning and Initializing Arrays in ExcelIn VBA, you do not use Set to assign an array: use ordinary assignment for array values, and…
- VBA Pivot Table Formatting: A Practical GuideTo format an existing Excel PivotTable with VBA, set its TableStyle2 property for an overall design, then set…
- TinyTask Macro Recorder: How It Works and When to Use ItTinyTask is a lightweight Windows macro recorder that captures mouse movements, clicks, and keyboard actions, then replays them…
- Excel VBA MsgBox Font Size: Change It or Use a FormYou cannot set the font size of Excel’s built-in VBA MsgBox directly: its text uses the standard Windows…
- VBA Set AutoFilter Range: Setup, Examples and FixesTo set an AutoFilter range in Excel VBA, identify a rectangular range that includes the header row, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.