Page 253 - Excel Progamming Weekend Crash Course

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

228 Saturday Evening

Table 18-2 ChartObject Methods

Method Description
BringToFront Displays the chart on top of any overlapping charts.
Copy Copies the ChartObject to the Windows Clipboard.
CopyPicture Copies an image of the chart to the Windows Clipboard.
Cut Cuts the ChartObject to the Windows Clipboard.

Delete Deletes the ChartObject.
SendToBack Displays the chart behind any overlapping charts.

The Placement property comes into play when cells are added or deleted in the area
under an embedded chart. For example, suppose a chart spans columns B through F, and
two new columns are inserted in this range. The result depends on the setting of the
Placement property:
xlMoveAndSize. The size and position of the chart are changed to accommodate
added or deleted rows/columns.
xlMove. The position of the chart is changed to accommodate added or deleted
rows/columns, but the size remains fixed.
xlFreeFloating. Neither the size nor the position is changed.
The program in Listing 18-1 illustrates using some of the ChartObject properties and
methods. This program goes through all worksheets in the current workbook. All embedded
charts are copied to a new worksheet. The name of this new worksheet, as well as the size
of the copied charts, are specified in arguments to the procedure. The program logic oper-
ates as follows:
1. Use the Worksheets.Add method to add a new worksheet to the workbook.
2. Set the new worksheet’s Name property.
3. Loop through the workbook’s Worksheets collection.
4. Check each worksheet’s Name property to avoid processing the new worksheet.
5. Loop through each worksheet’s ChartObjects collection.
6. Use the Copy method to copy each ChartObject to the Clipboard.
7. Use the Worksheet.Paste method to paste the ChartObject into the new
worksheet.

Listing 18-1 A program to copy embedded charts to a new worksheet

Public Sub CopyEmbeddedChartsToNewSheet(name As String, width As Integer, _
height As Integer)