Microsoft Office Tutorials and References
In Depth Information
Using VBA to Deactivate a Chart
What’s your name?
Every ChartObject object has a name, and every Chart contained in a ChartObject has a name.
That certainly seems straightforward enough, but chart names can be confusing. Create a new chart
on Sheet1 and activate it. Then activate the VBA Immediate window and type a few commands:
? ActiveSheet.Shapes(1).Name
Chart 1
? ActiveSheet.ChartObjects(1).Name
Chart 1
? ActiveChart.Name
Sheet1 Chart 1
? Activesheet.ChartObjects(1).Chart.Name
Sheet1 Chart 1
If you change the name of the worksheet, the name of the Chart also changes. However, you
can’t change the name of a Chart that’s contained in a ChartObject . This statement generates
an inexplicable “out of memory” error:
Activesheet.ChartObjects(1).Chart.Name = “New Name”
What about changing the name of a ChartObject ? The logical place to do so is in the Name
box (to the left of the formula bar). Although you can rename a shape by using the Name box,
you can’t rename a chart (even though a chart is actually a shape). To rename an embedded
chart, use the Chart Name control in the Chart Tools➜Layout➜Properties group. This control
displays the name of the active chart (which is actually the name of the ChartObject ), and
you can use this control to change the name of the ChartObject . Oddly, Excel allows you to
use the name of an existing ChartObject . In other words, you could have a dozen embedded
charts on a worksheet, and every one of them can be named Chart 1 .
Bottom line? Be aware of this quirk. If you find that your VBA charting macro isn’t working,
make sure that you don’t have two identically named charts.
Using the Location method also activates the relocated chart.
Using VBA to Deactivate a Chart
You can use the Activate method to activate a chart, but how do you deactivate (that is, unselect)
a chart? According to the Help System, you can use the Deselect method to deactivate a chart:
ActiveChart.Deselect
However, this statement simply doesn’t work.
 
Search JabSto ::




Custom Search