Page 365 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

Printing Charts
When printing a chart sheet, by default it will print on its own page. However, when working
with embedded charts, you need to determine if the chart should be printed on its own page
or with the worksheet. For a user to print an embedded chart on its own page, they are
required to first select the chart and then choose File, Print. This will allow the embedded
chart to be printed as if it were on a chart sheet.

The following procedure demonstrates how to preview all embedded charts in a worksheet as
full pages. To send the charts to the default printer, change the PrintPreview method to
PrintOut.

Sub PrintEmbeddedCharts()
For Each chtObj In ActiveSheet.ChartObjects
chtObj.Chart.PrintPreview
Next chtObj
End Sub

In contrast, if you would like to print the embedded chart as an object with the contents of
the worksheet, you can simply print the worksheet and the chart will be included automati­
cally. However, there might be situations where you want to exclude the chart when printing Chapter 15
the worksheet. To accomplish this task successfully, you must set the PrintObject property for
the ChartObject to False. By default, the ChartObject is included when printing a worksheet,
so the only time you need to set the PrintObject property is when you need to exclude the
embedded charts from the print job.
The following procedure prints the active worksheet and excludes all chart objects. Substitute
the PrintPreview property to PrintOut to send the print job to the default printer.
Sub PrintWorksheetOnly()
For Each chtObj In ActiveSheet.ChartObjects
chtObj.PrintObject = False
Next chtObj
ActiveSheet.PrintPreview
End Sub

Final Thoughts on Programming Charts

After reviewing the variety of options available when creating and modifying charts in a pro­
cedure, you’ll see that creating a programmatic reference to a chart on a chart sheet is easy.
The Chart object is a member of the Charts collection of the workbook. The challenge is set­
ting the reference to an embedded chart. You must be aware that the embedded Chart object
is contained in a ChartObject object that belongs to the ChartObjects collection of the work-
sheet. Remember, the Object Browser can be your greatest asset when troubleshooting your
VBA code.

339