Use Application.EnableCancelKey =
Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD)
user interruptions to the running procedure.
xlDisabled:
Cancel key trapping is completely disabled.
xlInterrupt:
The current procedure is interrupted, and the user can debug or
end the procedure.
xlErrorHandler:
The interrupt is sent to the running procedure as an error,
trappable by an error handler set up with an On Error GoTo statement.
The trappable error code is 18.
example:
On Error GoTo handleCancel Application.EnableCancelKey = xlErrorHandler MsgBox "This may take a long time: press ESC to cancel" For x = 1 To 1000000 ' Do something 1,000,000 times (long!) ' do something here Next x handleCancel: If Err = 18 Then MsgBox "You cancelled" End If
Remarks
Use this property very carefully. If you use xlDisabled, there's no way
to interrupt a runaway loop or other non self-terminating code.
Likewise, if you use xlErrorHandler but your error handler always
returns using the Resume statement, there's no way to stop runaway code.
The EnableCancelKey property is always reset to xlInterrupt whenever
Microsoft Excel returns to the idle state and there's no code running.
To trap or disable cancellation in your procedure, you must explicitly
change the EnableCancelKey property every time the procedure is called.
