Programming Excel with VBA and .NET

Print and Preview

Use the PrintOut or PrintPreview method to print or preview objects from code. These objects can print:

• Charts, Chart

• Range

• Sheets, Worksheets, Worksheet

• Window

• Workbook

The syntax and arguments for PrintOut and PrintPreview are the same for all objects, so see the Workbook object reference section in Chapter 8 for that information. These methods also apply to the current selection; for instance, the following code previews and prints the selected range:

Sub PrintSelection(  )
    ' Print with preview
    Selection.PrintOut , , , True
End Sub

Tip

In my sample code, I set the Preview argument to True so you can see what will print without wasting paper. Simply click Close on the preview window to cancel printing.

You can turn printing on or off for some objects embedded on a worksheet using the PrintObject property. The following code prints a worksheet but omits any embedded controls or other OLE objects:

Sub PrintWithOutObjects(  )
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ' Prevent printing of controls.
    ws.OLEObjects.PrintObject = False
    ' Print with preview.
    ws.PrintOut , , , True
    ' Restore printing for controls.
    ws.OLEObjects.PrintObject = True
End Sub

You can further control printing through the Workbook object’s BeforePrint event. For instance, this code prevents the user from printing any part of the workbook:

' ThisWorkbook module   ' Cancel print jobs before they are processed. Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True ' Display a message ...