XL:Can't Use PrintOut Method When Custom Dialog Box Is Visible

ID: Q148723

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, you cannot use the PrintOut or the PrintPreview method (to print a sheet or to view it in print preview) when a custom (Microsoft Excel version 5.0 or 7.0) dialog box is visible. If you try to use the PrintOut or the PrintPreview method while a custom (Microsoft Excel version 5.0 or 7.0) dialog box is visible, you receive one of the following error messages:

   PrintOut Method of Worksheet class Failed

    -or-

   Run-time Error '1004':
   PrintOut Method of Sheets Class Failed

CAUSE

This problem occurs because of the way in which Microsoft Visual Basic for Applications code and custom dialog boxes interact with Microsoft Excel. For example, to use the PrintOut method to send a document called "Sheet1" to your printer, you would normally use the following command:

   Worksheets("Sheet1").PrintOut

However, when a custom (Microsoft Excel version 5.0 or 7.0) dialog box is visible on the screen, you receive one of the error messages mentioned in the "Symptoms" section of this article.

WORKAROUND

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/refguide/

To work around this problem, use any of the following methods.

Method 1 (Excel 97 for Windows and Excel 98 Macintosh Edition Only)

Use a UserForm instead of a custom (Microsoft Excel version 5.0 or 7.0) dialog sheet. For example, do the following:

 1. In a new workbook type "hello" (without the quotation marks) in
    cell A1 of Sheet1, and then start the Visual Basic Editor.

 2. On the Insert menu, click UserForm.

 3. Add a command button to the UserForm.

 4. On the View menu, click Code to display the module sheet for the
    UserForm.

    On the UserForm1 (Code) module, you see the following code displayed:

       Private Sub CommandButton1_Click()

       End Sub

 5. Modify this code to read:

       Private Sub CommandButton1_Click()

          my_print_macro

       End Sub

 6. On the Insert menu, click Module.

    This will insert a basic module sheet into your project.

 7. On this module sheet, type the following code:

       Sub Show_Form()

          UserForm1.Show                ' This displays the UserForm.

       End Sub

       '---------------------

       Sub my_print_macro()

          Sheets("sheet1").PrintOut     ' This prints Sheet1.

       End Sub

 8. Run the Show_Form macro.

    The UserForm you created appears.

 9. Click the command button on the UserForm.

    Sheet1 of your workbook prints.

10. Dismiss the UserForm.

Method 2

You can use a method called tunneling to remove the custom (Microsoft Excel version 5.0 or 7.0) dialog box, perform your print action, and then redisplay the dialog box.

To use the PrintOut method, you must first hide or dismiss all custom (Microsoft Excel version 5.0 or 7.0) dialog boxes.

The following Visual Basic code example uses tunneling to display a custom (Microsoft Excel version 5.0 or 7.0) dialog box, to hide the dialog box before it prints a worksheet, and to then redisplay the dialog box when the print operation is complete.

This example assumes that you have a (Microsoft Excel version 5.0 or 7.0) dialog sheet called "Dialog1" (without the quotation marks) and a worksheet called "Sheet1" (without the quotation marks) that are located in the same workbook, and that you have a macro module with the following macro. The dialog sheet must contain two buttons: DoneButton and PrintButton.

Before you run the macro, you need to assign the appropriate macros to the DoneButton and the PrintButton. To do this, follow these steps:

1. Activate the dialog sheet.

2. Select the DoneButton.

3. On the Tools menu, click Assign Macro. (In Microsoft Excel 97, right-

   click the DoneButton, and then click Assign Macro on the shortcut menu.)

4. In the Assign Macro dialog box, click the DoneButton_Click macro, and
   then click OK.

To assign the PrintButton_Click macro to the PrintButton, repeat steps 1 through 4 above and substitute PrintButton for DoneButton and PrintButton_Click for DoneButton_Click.

To run the following example, position the insertion point in the line that reads "Sub MainMacro()," and then press the F5 key.

   '-----------------------------------------------------------------------
   Option Explicit
   Public DoneFlag As Integer, PrintFlag As Integer

   Sub MainMacro()
      PrintFlag = 0                         ' Initialize PrintFlag.
      DoneFlag = 0                          ' Initialize DoneFlag.
      DialogSheets("Dialog1").Show          ' Show it initially.

      ' While the DoneFlag does not equal 1 (which will only occur if the
      ' DoneButton is clicked), continue to loop through the Sub procedure.

      Do
         If PrintFlag = 1 Then              ' If the PrintFlag is set, then
            Worksheets("Sheet1"). PrintOut  ' print Sheet1 and
            PrintFlag = 0                   ' reset the PrintFlag.
            DialogSheets("Dialog1").Show    ' Reshow it only after
         End If                             ' having called the procedure
      Loop Until DoneFlag = 1               ' that hid it.
   End Sub

   Sub DoneButton_Click()
      DoneFlag = 1                          ' Set the DoneFlag.
      DialogSheets("Dialog1").Hide          ' Hide the dialog box.
   End Sub

   Sub PrintButton_Click()
      DoneFlag = 0                          ' Ensure DoneFlag set to 0.
      PrintFlag = 1                         ' Set the PrintFlag.
      DialogSheets("Dialog1").Hide          ' Hide the dialog box.
   End Sub
   '-----------------------------------------------------------------------

When you activate the DoneButton or the PrintButton button, the appropriate Sub procedure (DoneButton_Click or PrintButton_Click) runs. Within each Sub procedure, the Dialog1 dialog box is hidden and a flag (DoneFlag or PrintFlag) is set to 1. The MainMacro Sub procedure then resumes and loops back; if PrintFlag equals 1, the macro prints the worksheet and redisplays the dialog box; if DoneFlag equals 1, the macro exits the loop and ends the macro.

In this way, the PrintOut method is only executed if the Dialog1 dialog box is not visible on the screen, and the dialog box will reappear until you exit the loop by activating the DoneButton.

Method 3

This method provides another way to work around the behavior. Use the OnTime method to allow the macro that contains the DialogSheets("sheetname").Show to complete before you run the PrintOut method.

The following sample procedures illustrate this workaround. When you use this code, the print button dismisses the dialog box, and one second later, the print macro runs. One second after the sheet prints, the dialog box appears again.

Note the following regarding the sample procedures:

Method 4

This workaround checks to see if the Print button in the dialog box was clicked. If the button was clicked, the macro prints after the dialog box is dismissed.

1. Rename a button on your (Microsoft Excel version 5.0 or 7.0) dialog

   sheet to "print" (without the quotation marks).

   NOTE: Use the Name box on the Formula Bar to rename the button.

2. Assign the "Set_Flag" macro to this button.

3. Create a second button on the dialog sheet, rename it "other."

4. Assign the "Set_Flag" macro to this "other" button.

5. To be able to test the use of the "other" button, create another Excel

   5.0/7.0 dialog sheet and rename the sheet "Other."

6. Enter the following macros into a new module sheet in your workbook.

      '-----------------------------------------------------------------
      ' Makes the variables case insensitive.
      Option Compare Text

      ' Dimension a Public variable to determine which button is
      ' clicked in the dialog box.
      Dim Flag As String

      Sub Show_Dialog()

         ' Sets Flag = to nothing.
         Flag = ""

         ' Shows Main dialog box.
         Do While DialogSheets("Main").Show

         ' Checks to see which button was clicked.
         Select Case Flag

         ' If clicked "Print," run Print_Macro.
         Case "Print"
             Print_Macro

         ' After Print_Macro runs, exit the routine.
         ' Ignore this line if you want to have
         ' the Main dialog box pop up again.
         Exit Sub

         ' If clicked "Other," run Other_Macro.
         Case "Other"
             Other_Macro

         ' If clicked any other button, exit the macro.
         Case Else
            Exit Sub
         End Select
         Loop
      End Sub

      Sub Set_Flag()

         ' Sets the variable Flag to the button that calls this macro.
         Flag = Application.Caller
      End Sub

      Sub Print_Macro()

         ' Print macro goes here.
         ' This code can be placed under Case "Print" above without
         ' having this Sub procedure.
         MsgBox "Your Print Macro here"
      End Sub

      Sub Other_Macro()

         ' Shows secondary dialog.
         ' This code can be placed under Case "Other" above without
         ' having this Sub procedure.
         DialogSheets("Other").Show
      End Sub
      '--------------------------------------------------------------------

7. Run the "Show_Dialog" macro.

8. Click the button in your custom dialog box that was assigned to the

   "Set_Flag" macro.

9. Click OK in your dialog box to dismiss the dialog box.

   The "Print_Macro" runs and "Your Print Macro here" message appears in a
   message box.

REFERENCES

For more information about the PrintOut method, click the Index tab in Microsoft Excel Help, type the following text

   printout

and then double-click the selected text to go to the "PrintOut Method" topic.

For more information about the PrintPreview method, click the Index tab in Microsoft Excel Help, type the following text

   printpreview

and then double-click the selected text to go to the "PrintPreview Method" topic.

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 xl97user fail fails printing runtime run time chart

Keywords          : kbprg kbprint kbdta kbdtacode PgmOthr KbVBA xlprint kbfaq
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: May 17, 1999