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.
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.
Learn more about Allen...
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.
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 Loop
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:
Figure 1. The Protection tab of the Project Properties dialog box.
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 Next Application.EnableCancelKey = xlInterrupt Exit Sub ErrHandler: 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]", _ Buttons:=vbYesNo) If lContinue = vbYes Then Resume Else 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.
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!