XL: EnableCancelKey Property Ignored in Printing Procedure

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

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 will be 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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

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, do the following:

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.

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

   ARTICLE-ID: Q79488
   TITLE     : Excel: ON.KEY Does Not Disable Macro Interruption
               with ESC Key

   ARTICLE-ID: Q146864
   TITLE     : Error Trapping with Visual Basic for Applications

REFERENCES

"Visual Basic User's Guide," version 5.0, Chapter 9, "Advanced Error- Handling Techniques"

For more information about the EnableCancelKey property, click Answer Wizard on the Help menu and type:

    EnableCancelKey