Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Aborting a Macro and Retaining Control.

Aborting a Macro and Retaining Control

Written by Allen Wyatt (last updated December 3, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003


When you are developing a macro for others to use, you may want to add a method for the user to exit your macro before it ends, and still retain control of what the macro does. Ctrl+Break will stop a macro, but it doesn't exit gracefully, as it allows the user to view the code in the VBA Editor.

There are several ways you can approach this problem. The first is to build a "do you want to exit" prompt into your macro, and then have the macro display the prompt periodically. For instance, consider the following code:

Do ...

    '    your code goes here

    Counter = Counter + 1
    If Counter Mod 25 = 0 Then
        If MsgBox("Stop Macro?", vbYesNo) = vbYes Then End
    End If

The macro construction is based on the premise that you have a series of steps you want to repeat over and over again, through the use of a Do ... Loop structure. Every time through the loop, the value of Counter is incremented. Every 25 times through the loop, the "stop macro?" prompt is displayed, and the user has a chance to exit.

This approach is easy to implement and may work quite well for some purposes. The biggest drawback to this approach, however, is that it doesn't allow immediacy—the user must wait to exit the macro until at least 25 iterations have occurred.

Another approach is to "hide" the VBA code and apply a password to it. You do this by following these steps from within the VBA Editor:

  1. Choose the VBAProject Properties option from the Tools menu. The editor displays the Project Properties dialog.
  2. Make sure the Protection tab is displayed. (See Figure 1.)
  3. Figure 1. The Protection tab of the Project Properties dialog box.

  4. Choose the Lock Project for Viewing check box.
  5. In the Password box, enter a password you want used to protect the macro.
  6. In the Confirm Password box, enter the same password a second time.
  7. Click OK.

Close the VBA Editor, then save the workbook. With the VBA project protected, the user can still click Ctrl+Break to stop the macro, but they won't be able to get to the actual program code. They will only be able to choose from the Continue or End buttons, both of which protect your code. As an added benefit, this approach also restricts the user from viewing your code by using menu, toolbar, or ribbon choices.

Perhaps the best approach, however, is to create an error handler that will essentially take charge whenever the user presses Esc or Ctrl+Break. The handler that is run can then ask the user if they really want to quit, and then shut down gracefully if they do. Here's some example code that shows how this is done:

Sub Looptest()
    Application.EnableCancelKey = xlErrorHandler
    On Error GoTo ErrHandler

    Dim x As Long
    Dim y As Long
    Dim lContinue As Long

    y = 100000000
    For x = 1 To y Step 1

    Application.EnableCancelKey = xlInterrupt
    Exit Sub

    If Err.Number = 18 Then
        lContinue = MsgBox(prompt:=Format(x / y, "0.0%") & _
          " complete" & vbCrLf & _
          "Do you want to Continue (YES)?" & vbCrLf & _
          "Do you want to QUIT? [Click NO]", _
        If lContinue = vbYes Then
            Application.EnableCancelKey = xlInterrupt
            MsgBox ("Program ended at your request")
            Exit Sub
        End If
    End If

    Application.EnableCancelKey = xlInterrupt
End Sub

Notice that this example uses the EnableCancelKey method, assigning it the name of the label that should be jumped to if the cancel key (Esc or Ctrl+Break) is pressed. In this case, ErrHandler is jumped to, and the user is asked what to do. If the user chooses to exit, then the macro is shut down gracefully.

Notice that the first thing done after the ErrHandler label is to check if the Number property of the Err object is equal to 18. If it is, you know that a cancel key was pressed. If not, then some other type of error occurred, and it should be handled in whatever way is appropriate for your macro.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3021) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Aborting a Macro and Retaining Control.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Moving Text Using the Mouse

Many people use the keyboard to do their primary editing tasks. Word doesn't limit you to the keyboard, however. You can ...

Discover More

Changing a Toolbar Button Image

Changing the image of a button on a Toolbar in Word.

Discover More

Summing Absolute Values

You can easily sum a series of values in Excel, but it is not so easy to sum the absolute values of each value in a ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Automating Copying Macros

You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's ...

Discover More

Disabling Shift Key Use when Opening a Workbook

Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic ...

Discover More

Hiding Macros

Need to hide some macros in your workbook? There are three ways you can do it, as covered in this discussion.

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 9?

2023-11-10 12:59:02

Jeff Carnohan

The simplest method by far is Ctrl-Pause. If pops up the usual debug window so the user can cancel or continue. I only wish I'd known this trick years ago.

2023-11-09 09:50:46

Jan Hrachový

My solution is completely different and far much better and easy, since usually ESC or ctrl+break and other commands do not work. To prevent the data loss due to forced killing of Excel or Word, I use these steps in MS Windows 10:

1. open the command shell (click on Start button or press Win key on your keyboard, then type ‘cmd’ and select Run as administrator)
2. write: ‘shutdown /h’ (without quotes of course) and press Enter key - the computer goes to hibernation, which has higher priority than running VBA = this will break the macro safely
3. wake up the computer again and log in
4. now, you can see the pop-up prompt to end the macro… no data loss, no program killing

You’re welcome.

2018-07-27 12:06:55

J. Woolley

I had some problems with similar ErrHandler code but resolved them. For one thing, Err.Number is not always 18 after CancelKey; it depends on what Excel was doing when interrupted. For another thing, the MsgBox would not always activate; but that was resolved by putting DoEvents before MsgBox.

2017-03-10 03:04:02

Barry Fitzpatrick

It's difficult to determine what is going on without seeing it first hand. The most likely cause is a corruption of the Excel file, so I would go back to most recent working back-up.

2017-03-09 22:28:00


Hello, I have a excel database that I've created with three macro enabled buttons. The sheet was working perfectly fine excel froze on me. thinking that the problem was with one of my macros I pressed esc ctrl break and exited the macro. Well now my macro buttons won't work anymore. I keep getting a debug message which debugging still doesn't fix it. Everytime I press my macro buttons the VBA Editor opens up. How can I undo this?

2017-02-14 00:57:03


I have one vba query
Where when I use Application.hyperlink with different cell ranges..

Code is executing for all the cell ranges, but I want if one cell range is able to work.. the macro should stop executing next cell condition..

How to do macro opening all the cells in a specified range.

2016-11-29 11:09:19



Did you find out what the problem was?

2016-09-18 05:55:21



See my comments dated 28th May 2016.

2016-09-17 08:03:46


This does NOT work with Excel 2016. Neither the ESC Key nor the Ctrol-Break key will interrupt the loop and trigger an error.

I pasted the code as is and started the LoopTest sub from a macro button on one of my sheets in the workbook!

2016-05-28 05:28:54


IMHO, using Ctrl+Break will never cause a "clean" break in program execution, except for the simplest of macros. It is likely that here are several lines of code in the main loop of the macro, and there is a high probability that the Ctrl+Break will be actioned during the execution of this code. Therefore the some lines will have been executed and others will not have been executed in the pass through this code when the Ctrl+Break occurs, this will leave the data in an unknown state.

A better way would be to have button control on the visible worksheet or a modeless Userform which when clicked would set a flag which the main macro tests at the end of each pass through the loop, together with a DoEvents instruction to allow the user the opportunity to click the "Stop" button. I would also recommend disabling the Ctrl+Break function during the execution of the code (and re-enabling at the end of course) to prevent interruption in this way. These extra lines of code will slow the macro down a bit but are worthwhile to ensure things don't get messed up.

This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.