EnableCancelKey Property Ignored in Printing Procedure (151316)

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:

1. 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
   						

2. Activate Sheet1 in this workbook.
3. Run the Test_Esc macro.
4. 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:

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