Page 261 - Excel Progamming Weekend Crash Course

n540629 ch18.qxd 9/2/03 9:35 AM Page 236

236 Saturday Evening

Refer to Session 3 for more details on using the PrintOut method.

Cross-Ref
You may have noticed that there is no direct way to print all of the embedded charts on
a worksheet without also printing the other worksheet contents. This oversight is easily
remedied with a short VBA program, shown in Listing 18-3. This procedure takes a work-
sheet reference as its one argument. It then loops through the worksheet’s ChartObjects
collection, calling the PrintOut method on each contained chart. If the worksheet contains
no embedded charts, nothing happens.

Listing 18-3 Printing all embedded charts in a worksheet

Public Sub PrintAllEmbeddedChartsOnSheet(ws As Worksheet)

‘ Prints all embedded charts on the specified
‘ worksheet.

Dim co As ChartObject
For Each co In ws.ChartObjects
co.Chart.PrintOut
Next

End Sub
Public Sub TestPrintAllCharts()

PrintAllEmbeddedChartsOnSheet ActiveSheet

End Sub

When printing a worksheet, remember that the ChartObject’s PrintObject
property determines whether that specific embedded chart is printed along
Tip with the other worksheet contents.
To test this procedure, you need another procedure containing a single line of code to
call it and pass a reference to the correct worksheet. Such a testing procedure is shown in
Listing 18-3 as well, printing the charts in the active workbook.

When individual embedded charts are printed using the default settings,
they are automatically scaled to fill the printed page regardless of the size
Note of the chart in the worksheet.