XL: EnableCancelKey Property Ignored in Printing Procedure

ID: Q151316

The information in this article applies to:

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 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, 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 professionals 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/overview/overview.asp

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:

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

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

REFERENCES

For more information about the EnableCancelKey Property in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type enablecancelkey, click Search, and then click to view "EnableCancelKey Property."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions

"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

Additional query words: 5.00a 5.00c 7.00a 8.00

Keywords : kbprg kbprint kbdta kbdtacode PgmOthr KbVBA xlprint
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,97
Platform : MACINTOSH WINDOWS
Issue type : kbhowto