Charts are packed with different objects, so manipulating charts with VBA can be quite confusing. To get a feel for this, turn on the macro recorder, create a chart, and perform some routine chart-editing tasks. You may be surprised by the amount of code Excel generates. After you understand the objects in a chart, however, you can create some useful macros.
To write macros that manipulate charts, you must understand some terminology. An embedded chart on a worksheet is a ChartObject object. You can activate a ChartObject much like you activate a sheet. The following statement activates the ChartObject named Chart 1:
After you activate the chart, you can refer to it in your VBA code as the ActiveChart. If the chart is on a separate chart sheet, it becomes the active chart as soon as you activate that chart sheet.
When you click an embedded chart, Excel actually selects an object inside the ChartObject object. You can select the ChartObject itself by pressing Ctrl while clicking the embedded chart. Select the ChartObject if you want to change an embedded chart's name. After selecting the ChartObject object, use the Name box (the control to the left of the formula bar) to change the name.
Was this article helpful?