Microsoft KB Archive/151316

= EnableCancelKey Property Ignored in Printing Procedure =

Article ID: 151316

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q151316



SYMPTOMS
Microsoft Visual Basic for Applications in Microsoft Excel includes a property, EnableCancelKey, that can trap or ignore attempts to interrupt a procedure that is currently running. This property setting may be ignored when you press CTRL+BREAK or ESC (or COMMAND+PERIOD on the Macintosh).



CAUSE
The EnableCancelKey property is ignored when you are printing within a Visual Basic procedure.



WORKAROUND
You cannot work around this issue.

NOTE: You can test to see if the printing was successful, but you cannot stop the user from interrupting the printing. For an example of how to accomplish this task with a macro, see the macro provided in the "More Information" section of this article.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Printing in a Visual Basic for Applications procedure is a function of Microsoft Windows, not Microsoft Excel. Therefore, error trapping for user interrupts would be ineffective because error trapping only applies for Microsoft Excel actions. You can, however test to see if printing was successful and take the appropriate steps. The following procedure traps for user-interrupted or unsuccessful printing by assigning the Visual Basic statement that performs the printing to a variable and testing the value of the variable. Determining if the user interrupted the printing this way can be tricky because the printing could also be unsuccessful for other reasons, such as no default printer, a blank worksheet is active, and so on. The following sample procedure demonstrates in-line error handling with user-interrupts disabled.

Handling Printing Errors Programmatically
' This macro uses in-line error checking and assumes that the user ' does not have the ability to interrupt the macro.

Sub InLinePrintHandling Dim Printout_Successful As Boolean Dim response As Integer

' Because this macro is an example of In-Line error handling, ' it's assumed that errors are handled immediately after the ' line is executed. Therefore, on every error we're resuming ' to the next line where there should be an IF test for ' possible errors. On Error Resume Next

Application.EnableCancelKey = xlDisabled ' Disable user interrupts.

Printout_Successful = False  ' Initialize the printing variable

Do        ' If there is some problem printing, Printout_Successful will ' have a value of False. Printout_Successful = ActiveSheet.PrintOut

If Not Printout_Successful Then

' Display a message box to ask the user if they would like to           ' retry printing or to cancel printing. response = MsgBox("Printing failed due to your interruption " _              & "or some other problem. Do you want to Retry " & _               "printing or Cancel printing and continue with" & _               " the macro?", vbRetryCancel + vbCritical + _               vbApplicationModal, "Print failure!")

' If you do not want to retry printing, exit the loop. If response = vbCancel Then Exit Do

End If

Loop Until Printout_Successful

End Sub Run the macro above from a new worksheet that does not have any data. The message box in the macro will appear because there will be a printing error due to the fact there is nothing to print in your worksheet.

Trapping Escape Key with Error Handler
When a Visual Basic procedure is running, you can usually interrupt the procedure by pressing CTRL+BREAK or ESC (or COMMAND+PERIOD on the Macintosh). If you allow your procedure to be interrupted, you can make sure your procedure is notified so that it 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 set, all interrupts generate run-time error number 18, which you can trap using an On Error statement. You can handle this error to halt the procedure and exit the program. If you use the Resume statement to continue after a trapped run-time error, however, the interrupt is ignored. You can also ignore user interrupts completely by setting the EnableCancelKey property to xlDisabled. To see an example of this, follow these steps:   Enter the following in a new module sheet in a new workbook. Sub Test_Esc

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!) ActiveSheet.Range("A1").Offset(x - 1, 0).Value = x        Next x

handleCancel: If Err = 18 Then        '18 is the error returned when the MsgBox "You canceled" 'EnableCancelKey property is set to           End If                   'xlErrorHandler.

End Sub  Activate Sheet1 in this workbook. Run the Test_Esc macro. Click OK in the message box informing you "This may take a long time..." and then press ESC.

Another message box will appear informing you that you canceled the procedure.

For additional information, please see the following articles in the Microsoft Knowledge Base:

79488 Excel: ON.KEY Does Not Disable Macro Interruption with ESC Key

146864 Error Trapping with Visual Basic for Applications

NOTE: You also cannot suppress the display of the printing dialog boxes by using the DisplayAlerts property or ScreenUpdating property.

