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:
(ByVal hMidiOut As Long) As Long
and this part was modified:
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))
DoEvents
Next r
ProcExit:
On Error Resume Next
midiOutReset hMidiOut
Stop
Exit Sub
ErrHandler:
If Err.Number <> 18 Then
MsgBox Err.Description
Else
Debug.Print Err.Description
End If
Stop
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.
The culprit is DoEvents. When removed Esc will cancel correctly.
It works the same with or without DoEvents for me.
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.
Rob
I had a similar problem with MsgBox in my EnableCancelKey error handler. It was resolved when I put DoEvents before MsgBox. For example,
ErrHandler:
If Err.Number 18 Then
DoEvents
MsgBox Err.Description
…