Page 251 - Excel Progamming Weekend Crash Course

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

226 Saturday Evening

You can rename a ChartObject by assigning the new name to the Name property. You
cannot rename a Chart object that is an embedded chart, even though the Microsoft docu-
mentation claims you can. That’s not a problem because embedded charts are accessed by
means of the ChartObject.Name property, not the Chart.Name property. If you will need to
refer to a chart later in code, it is a good idea to assign a meaningful name to
ChartObject.Name.
To refer to an existing chart sheet, you use the Workbook object’s Charts property. This
property returns a collection containing every chart sheet in the workbook. You can refer-
ence a chart sheet by its position in the collection, but this is not particularly useful
because you usually do not know what a chart’s position is. It is better to reference by the
chart sheet’s name:
Charts(ChartName)

If you try to reference an element that does not exist in a collection, an
error occurs.
Note
For example, this line of code prints the specified chart sheet:
Charts(“Sales Summary”).PrintOut

To reference a specific embedded chart, you must access it by means of its ChartObject.
This is done using the worksheet’s ChartObjects collection. As with other collections, you
can access individual items by index or name. This code deletes the specified ChartObject
(and its contained chart) on Sheet1:
Worksheets(“Sheet1”).ChartObjects(“Summary”).Delete

There is no way to directly access all of the embedded charts in a workbook — you must
look for embedded charts on each worksheet separately. An example of this is presented
later in this session in Listing 18-3.

Locking Charts
By default, a chart in Excel can be modified by the user; however, there may be times when
you do not want to permit any modifications. You can lock or protect a chart so that the
user can view and print it, but cannot make any changes to the chart. To lock and protect a
chart, call the Protect method on the Chart object:

SomeChart.Protect(password)
Password is an optional argument that specifies a case-sensitive password for unprotect-
ing the chart. If omitted, the user (or another program) can remove protection from the
chart and then modify the chart without restrictions. If included, the password must be
provided to unprotect the chart. This is done with the Unprotect method:

SomeChart.UnProtect(password)