Page 355 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Charts

specify which chart to modify when the procedure is executed. Modify the previous proce­
dure to include the chart reference.

Sub ModifySpecificChart()
With Sheets(“Sheet1”).ChartObjects(“Chart1”).Chart
.Type = xlArea
.ChartArea.Font.Name = “Tahoma"
.ChartArea.Font.FontStyle = “Regular"
.ChartArea.Font.Size = 8
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue).TickLabels.Font.Bold = True
.HasLegend = True
.Legend.Position = xlLegendPositionBottom
End With
End Sub

Inside Out

To Use or Not to Use Chart Events
Chapter 15
An event is used to monitor an object. Your chart is considered an object regardless of its
location. So if you have a specific event that in turn requires a specific action, you should
use Chart events.

Some of the triggers available for a Chart object are Activate, MouseDown, MouseMove, and
SeriesChange. To write an event procedure for an embedded chart, you must create a new
object using the WithEvents keyword in a class module and declare an object of type Chart
with events.
Consider the following example. Assume a new class module is created and named Event-
ClassModule. The new class module contains the following WithEvents statement:

Public WithEvents myChartClass As Chart
After the new object has been declared with events, it appears in the Object drop-down list
in the class module. You can now create an event procedure for this object. However, before
the procedure will run, you must connect the declared object with the embedded chart. The
following code can be used in any module to achieve the required result:
Dim myClassModule As New EventClassModule

Sub InitializeChart()
Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

After the InitializeChart procedure has been executed, the myChartClass object in the class
module points to the first embedded chart on the first worksheet in the workbook. All event
procedures in the class module for the object will now be evaluated as the triggers occur.

329