Microsoft KB Archive/105876

From BetaArchive Wiki

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


The information in this article applies to:
- Microsoft Excel for Windows, version 5.0 - Microsoft Excel for the Macintosh, version 5.0


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.


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:


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


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.