Handling User Interrupts

When a Visual Basic procedure is running, a user can interrupt it by pressing CTRL+BREAK or ESC. Although you may need to be able to interrupt procedures when you're debugging, you might want to prevent the user from interrupting procedures in the finished application. If you do allow user interrupts in a finished application, however, you can make sure that your procedures are notified of them so that the procedures can close files, disconnect from shared resources, or restore modified variables before returning control of the application to the user.

You can trap user interrupts in your procedures by setting the EnableCancelKey property to xlErrorHandler. With this property setting in effect, all interrupts generate run-time error 18, which you can trap using an On Error statement. You can handle this error to halt the procedure and exit the program. However, if you use the Resume statement to continue running the procedure after a run-time error has been trapped, the interrupt is ignored.

The following example illustrates a procedure that requires a long time to complete. If a user interrupts the procedure while it's running, an error is trapped — first to confirm that the procedure should be stopped, and then to exit the procedure in an orderly way. Note that you can use only one interrupt handler for each procedure, and that the same handler is used for all other run-time errors encountered in that procedure.

Sub ProcessData
    ' Set up user interrupt trapping as a run-time error
    On Error GoTo UserInterrupt
    Application.EnableCancelKey = xlErrorHandler
    'Start a long duration task
    OpenDataFile
    For x = 1 To 1000000
        ProcessRecord x
        WriteOutRecord x
    Next x
    CloseDataFile
    Exit Sub

UserInterrupt:
    If Err = 18 Then
        If MsgBox ("Stop processing records?" , vbYesNo) = vbNo Then
            ' Continue running at the point we were interrupted
            Resume
        Else
            ' Close open files before returning
            CloseDataFile
            Exit Sub
        End If
    Else
        ' Handle other errors that occur
        MsgBox Error(Err)
        Resume Next
    End If
End Sub

You can also ignore user interrupts completely by setting the EnableCancelKey property to xlIgnore. With this property setting in effect, Microsoft Excel ignores all attempts by the user to interrupt the procedure that's running.

Note

Use caution when disabling or ignoring user interrupts. It's possible to write code that never returns or ends. If you disable interrupts by setting the EnableCancelKey property to xlIgnore, or if you always use the Resume statement to return from a trapped error, your procedure won't return control to the user.

You can restore the default interrupt processing of Microsoft Excel by setting the EnableCancelKey property to xlInterrupt. To prevent a procedure from permanently turning off user interrupts, Microsoft Excel always restores the value of the EnableCancelKey property to xlInterrupt whenever code execution ends. To ensure that interrupts are handled properly, you must write your procedure so that it explicitly disables or traps interrupts each time it's run.