The EnableCancelKey property of the Application object determines what will happen when a user interrupts your code. Users can interrupt code by pressing Esc or Cntl+Break. EnableCancelKey has three possible settings:

  • xlDisable – Prevents the user from interrupting. I’ve never used this and can’t think of why I ever would.
  • xlInterrupt – Normal operation. The debugger is shown and the code is in debug mode at whichever line it happened to be when it was interrupted.
  • xlErrorHandler – Raises error number 18 and reacts just like any other error. If you have error handling set up, it’s called.

J-Walk posted about Musical Excel a while back. There was an issue with the midi out API not resetting properly and people could not run the code twice. Mpemba fixed it in the comments with a reset API call, but if the user was to interrupt the code, that API function would never get called.

I downloaded the dueling banjos file from J-Walk, and modified the code. This part was added:

Private Declare Function midiOutReset Lib “winmm.dll” _
    (ByVal hMidiOut As Long) As Long

and this part was modified:

Sub PlayWorksheetNotes()
    Dim r As Long
    On Error GoTo ErrHandler
    Application.EnableCancelKey = xlErrorHandler
    For r = 2 To Application.CountA(Range(“A:A”))
        Cells(r, 2).Select
        Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
    Next r
    On Error Resume Next
    midiOutReset hMidiOut
    Exit Sub
    If Err.Number <> 18 Then
        MsgBox Err.Description
        Debug.Print Err.Description
    End If
    Resume ProcExit
End Sub

Setting EnableCancelKey allows the code to exit gracefully and ensures that the midiOutReset call is always made. This resets the midi out and should prevent the problem of not being able to run the code twice. It shouldn’t really matter whether you have an On Error statement before or after the EnableCancelKey statement.

Actually, this code would exit gracefully except that I have all those ‘Stop’ commands in there. Oddly, when I set EnableCancelKey to xlInterrupt, it works every time I press Cntl+Break (that is, the code stops and the debugger opens). When I set EnableCancelKey to xlErrorHandler, pressing Cntl+Break works about one out of every thousand times. That estimate is based on me pressing it about 100 times during code execution and the code executes all the way through nine out of ten times.

If you have the time, download dueling-banjos.xls, modified the code as I have and see if you can interrupt normally.

Posted in Uncategorized

4 thoughts on “EnableCancelKey

  1. I’ve recently used the Cancel Key functionality.

    My program progresses through each row of a worksheet, using the values as parameters to a database procedure.
    If a user were to press the Esc key, the transactions would sit in an uncommitted state.
    If the user were then to re-reun the processing, duplicates or contraint errors would result.

    The solution was to use EnableCancelKey to trap the Esc key and Rollback the transactions.


  2. I had a similar problem with MsgBox in my EnableCancelKey error handler. It was resolved when I put DoEvents before MsgBox. For example,
    If Err.Number 18 Then
    MsgBox Err.Description

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.