6.3 Response to Program Interrupts

VBA programs can usually be interrupted with Ctrl+Break . During the test phase of program development this is a great convenience, but in finished applications such program interrupts are usually unwelcome. If you wish to make it impossible for the user to interrupt your program with Ctrl+Break , then you have two options:

With Application. EnableCancelKey = xlDisabled you achieve the result that Ctrl+Break has no effect whatsoever. The advantage of this measure is that only one instruction (in the Auto_Open procedure) is necessary.
On the other hand, if you set the constant xlErrorHandler with EnableCancelKey , then each time the user hits Ctrl+Break an error with error number 18 occurs. You can catch this "error" just as you would catch any other error. The disadvantage is obvious: Each procedure must be equipped with an error-handling routine. Another variant consists in allowing interrupts only in those program segments in which very time- intensive calculations are carried out.

The "normal" reaction to interrupts, that is, the display of an alert, can be reinstated with the instruction EnableCancelKey=xlInterrupt .

Example

 ' example file miscellaneous.xls, Module1 Sub  slowcode  ()   Application.EnableCancelKey = xlErrorHandler   On Error GoTo slow_error   '   ' ... the actual procedure   '   Exit Sub slow_error:   If Err = 18 Then     result = MsgBox("Should the program be continued?", vbYesNo)     If result = vbYes Then Resume Next   End If   ' otherwise, interrupt procedure   '   '.. cleanup tasks End Sub

The above code segment from the example file miscellaneous.xls shows how an orderly response to Ctrl+Break can be achieved. The complete program code, which also demonstrates the control of the status bar and execution of background calculations, can be found in the last section of Chapter 5.