You cannot use the PrintOut method when the custom dialog box is visible

Method 1

1. In a new workbook type hello 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

1. Activate the dialog sheet.
2. Select the DoneButton.
3. 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.

   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
				

Method 3

• Sub ShowTheDialog can be much longer, but the .Show line should be the last line of the Sub procedure.
  
  
• Sub PrintTheSheet is a macro attached to a button that has been set to have the Dismiss property.
  
  
• Sub BackgroundPrint contains the actual PrintOut method.
  
  
• Sub ShowTheDialog is an optional macro to bring the dialog box back.

      Sub ShowTheDialog()
         ' This portion of the macro could be much larger,
         ' setting variables and conditions prior to displaying the dialog
         ' box.
         ' The last line should be this:
         DialogSheets("Dialog1").Show
      End Sub


      Sub PrintTheSheet()
         ' This macro should be attached to a button with the Dismiss
         ' property set and should contain only this line:
         Application.OnTime Now + TimeValue("00:00:01"), "BackgroundPrint"
      End Sub


      Sub BackgroundPrint()
         ' Like ShowTheDialog, this macro can be much larger,
         ' selecting areas, defining print ranges, or whatever before
         ' printing.
         ActiveSheet.PrintOut
         ' This line is optional; use it as the last line if you want the
         ' dialog box to reappear.
         Application.OnTime Now + TimeValue("00:00:01"), "RedisplayDialog"
      End Sub


      Sub RedisplayDialog()
         ' This optional macro recalls the dialog box without resetting any
         ' variable, and should contain only this line:
         DialogSheets("Dialog1").Show
      End Sub
				

Method 4

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 Print 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 Print 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.
   
   Print_Macro runs and "Your Print Macro here" message appears in a message box.