How do I allow a user to print from a button on a custom dialog box?
	To have my company name at the top of every printed page of all my worksheets, do I need to write complicated macros?
	How do I change the default printer and restore it, via code?

How do I allow a user to print from a button on a custom dialog box?
Posted November 11, 1996
Keywords: Print Button

Unfortunately, VBA will not allow you to print while a custom dialog is showing. What you need to do is have your Print button formatted to dismiss the dialog and also assign it to a procedure that sets a flag. You can then use this flag to decide whether to print after the dialog has disappeared.

The macros below demonstrate this method. Assign your print button to the SetFlag macro and also format it to dismiss the dialog (right-click on the button and choose Format Object - Control - Dismiss). Then use the ShowDialog macro to show the dialog.

The following two macros demonstrate how to print from a dialog

Dim Flag As Boolean

Sub ShowDialog()
    Flag = False
    DialogSheets("PrintDialog").Show
    If Flag Then Sheets("Sheet1").PrintOut
End Sub

Sub SetFlag()   ' This macro is assigned to the print button
    Flag = True
End Sub
	

To have my company name at the top of every printed page of all my worksheets, do I need to write complicated macros?
Keywords: Print Template XLSTART
Posted July 22, 1996

No. You can set up a worksheet template so that the page header contains your company name. Create a new workbook consisting of a single worksheet. Choose the File>Page Setup menu option and select the Header/Footer tab. Click the Custom Header button and type in your company name in the left, middle or right section. Change its format if required. Use the File - Save As menu option and save the file as a Template type (not as a Microsoft Excel Workbook) with the name "Sheet.XLT" in the Excel startup directory (usually XLSTART). Now, whenever you print a worksheet, it will have your company name at the top of every page. If you also have a workbook template (named Book.XLT), then remember to make identical changes to any worksheets in that template.

How do I change the default printer and restore it, via code?
Keywords: Printer ActivePrinter
Posted April 22, 1996

Use the ActivePrinter property of the Application object. It is a read-write string property so you can have code similar to the following.

Dim mszPrevPrinter As String     ''' Module-level var to restore current printer
Sub MyProcedure()
   ChangePrinter "microsoft fax on fax:"
   ''' Do something (print, maybe?!)...
   RestorePrinter
End Sub
Sub ChangePrinter(szPrinterName As String)
   mszPrevPrinter = Application.ActivePrinter
      ''' Change default printer
   Application.ActivePrinter = szPrinterName
   MsgBox "Changed to '" & Application.ActivePrinter & "' from '" & mszPrevPrinter & "'."
End Sub
Sub RestorePrinter()
      ''' Restore default printer
   If Application.ActivePrinter <> mszPrevPrinter Then
      Application.ActivePrinter = mszPrevPrinter
      MsgBox "Restored to '" & Application.ActivePrinter & "'."
   End If
End Sub
			

Back to Top

Back to the FAQ Table of Contents

(A)bort, (R)etry, (G)et a life?