Page 353 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

sheets. Because individual charts can also be embedded in worksheets and dialog sheets, the
Chart objects in the Charts collection can be accessed using the Item property. Either the
name of the chart can be specified as a parameter to the Item’s parameter or it can be an index
number describing the position of the chart in the workbook from left to right.

The Chart object allows access to all of the attributes of a specific chart in Excel. This includes
chart formatting, chart types, and chart-positioning properties. The Chart object also
exposes events that can be used programmatically.

Event procedures are executed when the appropriate trigger is initiated. Specific events can also be
monitored at the Chart level. For more information, review the side bar, “To Use or Not to Use Chart
Events.” For a review of event procedures at the Application, Workbook, and Worksheet levels, refer to
Chapter 12, “Understanding and Using Events.”

The ChartObjects collection represents all the ChartObject objects on a specified chart sheet
or worksheet. The ChartObject object acts as a container for a Chart object. Properties and
methods for the ChartObject object determine the appearance and the size of the embedded
chart on the worksheet. The ChartObject object is a member of the ChartObjects collection.
The ChartObjects collection contains all the embedded charts on a single sheet.
Use ChartObjects(index), where index is the embedded chart index number or name, to Chapter 15
return a single ChartObject object. In the following example, the chart name has been set to
SampleChart in an embedded chart on the worksheet named “Sheet1.”

Worksheets(“Sheet1”).ChartObjects(“Chart 1”).Name = “SampleChart”
The embedded chart name is shown in the Name box when the embedded chart is selected.
Use the Name property to set or return the name of the ChartObject object. The following
example puts rounded corners on the embedded chart named “SampleChart” on the work-
sheet named “Sheet1.”

Worksheets(“Sheet1”).ChartObjects(“SampleChart”).RoundedCorners = True

Manipulating Charts

A procedure can be used to create a chart; however, you’ll commonly create macros that will
modify existing charts. For example, a procedure can be used to streamline the formatting of
all the embedded charts within a workbook for the Garden Company. The procedure might
include resizing all charts to a standard size, specifying the chart location within a worksheet,
or even adding the company name in a label using the company colors.

Activating a Chart

A chart is activated when a user selects a chart, regardless of its location. Using VBA code, you
can activate an embedded chart using the Activate method.

ActiveSheet.ChartObjects(“Chart1”).Activate

327