XL5: Can't Use PrintOut When Custom Dialog Box Is Visible
PSS ID Number: Q105876
Article last modified on 10-20-1994
PSS database name: EXCEL

5.00    | 5.00
WINDOWS | MACINTOSH

----------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Excel for Windows, version 5.0
 - Microsoft Excel for the Macintosh, version 5.0
----------------------------------------------------------------------

SUMMARY
=======
Because of the way in which Visual Basic code and custom dialog boxes
interact in Microsoft Excel version 5.0, it is not possible to use the
PrintOut method, which allows you to print a document, or the PrintPreview
method, which allows you to print preview a document, while a custom dialog
box is visible on the screen. Instead, you must use a method called
"tunneling" to remove the dialog box, perform your print action, and
redisplay the dialog box. The following information discusses methods that
you can use to accomplish this task.

MORE INFORMATION
================
In Microsoft Excel version 5.0, you can use the PrintOut method to send a
document to your printer. For example, to print a worksheet called Sheet1,
you would use the command:

   Worksheets("Sheet1").PrintOut

When a custom dialog box is visible on the screen, if your macro uses the
PrintOut method, you may receive the error message:

   PrintOut method of Worksheet class failed

   -or-

   Runtime error 1004
   Printout Method of sheets class failed

To use the PrintOut method, you must first hide or dismiss all custom
dialog boxes.

Visual Basic Code Example
-------------------------
Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided 'as is' and
Microsoft does not guarantee that it can be used in all situations.
Microsoft does not support modifications of this procedure to suit customer
requirements for a particular purpose. Note that a line that is preceded by
an apostrophe introduces a comment in the code--comments are provided to
explain what the code is doing at a particular point in the procedure. Note
also that an underscore character (_) indicates that code continues from
one line to the next. You can type lines that contain this character as one
logical line or you can divide the lines of code and include the line
continuation character. For more information about Visual Basic for
Applications programming style, see the "Programming Style in This Manual"
section in the "Document Conventions" section of the "Visual Basic User's
Guide."
This Visual Basic code example uses tunneling to display a dialog box, hide
the dialog box before it prints a worksheet, and then redisplay the dialog
box when the print operation is complete.
This example assumes that you have a dialog sheet (Dialog1) and a worksheet
(Sheet1) that are located in the same workbook. The dialog sheet contains
two buttons: DoneButton and PrintButton.
Before executing 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. From the Tools menu choose Assign Macro.
4. Select the DoneButton_Click macro and choose OK.
To assign the PrintButton_Click macro to the PrintButton, repeat steps 1
through 4 and substitute PrintButton for DoneButton and PrintButton_Click
for DoneButton_Click.
To run the example, position the cursor in the line that reads "Sub
MainMacro()" and either press the F5 key or choose Start from the Run menu.

'-----------------------------------------------------------------------
Option Explicit
Public DoneFlag As Integer, PrintFlag As Integer
Sub MainMacro()
   PrintFlag = 0                         'initialize PrintFlag
   DoneFlag = 0                          'initialize DoneFlag
   'While the DoneFlag does not equal 1 (which will only occur if the
   'DoneButton is clicked), continue to loop through the Subroutine.
   Do
      If PrintFlag = 1 Then              'if the PrintFlag is set, then
         Worksheets("Sheet1").PrintOut   'print Sheet1 and
         PrintFlag = 0                   'reset the PrintFlag
      End If
      DialogSheets("Dialog1").Show       'display the dialog box
   Loop Until DoneFlag = 1               'loop until DoneButton clicked
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 either of the DoneButton or PrintButton buttons are activated, the
appropriate subroutines (DoneButton_Click or PrintButton_Click) are run:
within each subroutine, the Dialog1 dialog box is hidden and a flag
(DoneFlag or PrintFlag) is set to 1. The MainMacro subroutine 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 be redisplayed until
you exit the loop by activating the DoneButton.

KBCategory: kbprint
KBSubcategory:
Additional reference words: 5.00 chart

=============================================================================
Copyright Microsoft Corporation 1994.