Microsoft Office Tutorials and References
In Depth Information
Creating Charts with VBA
Assigning a Macro to a ChartObject
It’s possible to assign a macro to an embedded chart. To do so, Ctrl+click the chart
(to select the Chart object’s container — a ChartObject object); then, right-click
and choose Assign Macro from the shortcut menu. This displays the Assign Macro
dialog box, in which you can select the macro to be executed.
After assigning the macro, clicking the embedded chart will execute the macro. If you
assign the macro that follows, for example, clicking the chart will display a message
box that contains the chart’s name (the Caller property returns the name of the
object that called the procedure).
Sub Chart_Click()
Dim ChtName As String
ChtName = Application.Caller
MsgBox “You clicked “ & ChtName
End Sub
Also, be aware that this macro makes it difficult to edit the chart because the chart
isn’t activated when it is clicked. To edit the chart, right-click and choose Edit Chart
Object from the shortcut menu.
This macro does not preserve gradients or patterns applied to a series, and
it will overwrite any existing pictures pasted to the series.
Creating Charts with VBA
This section presents methods to create charts using VBA, and also discusses some of
the potential problems that may result when you use recorded macros. VBA provides
two methods to create a chart:
The Add method of the Charts collection. This is used to add a new
Chart object (that is, a Chart sheet).
The Add method of the ChartObjects collection. This is used to add a
new ChartObject (embedded chart).
Search JabSto ::

Custom Search