Microsoft Office Tutorials and References
In Depth Information
Moving a Chart
When a chart is activated, you can refer to it in your code by using the ActiveChart property
(which returns a Chart object). For example, the following instruction displays the name of the
active chart. If there is no active chart, the statement generates an error:
MsgBox ActiveChart.Name
To modify a chart with VBA, it’s not necessary to activate it. The two procedures that follow have
exactly the same effect. That is, they change the embedded chart named Chart 1 to an area
chart. The first procedure activates the chart before performing the manipulations; the second
one doesn’t:
Sub ModifyChart1()
ActiveSheet.ChartObjects(“Chart 1”).Activate
ActiveChart.ChartType = xlArea
End Sub
Sub ModifyChart2()
ActiveSheet.ChartObjects(“Chart 1”).Chart.ChartType = xlArea
End Sub
Moving a Chart
A chart embedded on a worksheet can be converted to a chart sheet. To do so manually, just
activate the embedded chart and choose Chart Tools➜Design➜Location➜Move Chart. In the
Move Chart dialog box, select the New Sheet option and specify a name.
You can also convert an embedded chart to a chart sheet by using VBA. Here’s an example that
converts the first ChartObject on a worksheet named Sheet1 to a chart sheet named
MyChart :
Sub MoveChart1()
Sheets(“Sheet1”).ChartObjects(1).Chart. _
Location xlLocationAsNewSheet, “MyChart”
End Sub
The following example does just the opposite of the preceding procedure: It converts the chart
on a chart sheet named MyChart to an embedded chart on the worksheet named Sheet1 .
Sub MoveChart2()
Charts(“MyChart”) _
.Location xlLocationAsObject, “Sheet1”
End Sub
Search JabSto ::

Custom Search