Page 354 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

To activate a chart on an individual chart sheet, you can use the following statement:

Sheets(“Chart1”).Activate
When a chart is activated, you can refer to it in your code as ActiveChart. This is a great way
to simply your code. For troubleshooting purposes, you can verify which chart has been acti­
vated by adding a message box to display the chart name. Once you have verified that the
correct chart has been activated, you can add a single quote at the beginning of the line that
calls the message box to make the line a comment. Making the line a comment means the line
won’t be executed. You can, of course, delete the code after you have completed testing the
procedure.

MsgBox ActiveChart.Name
When creating a procedure that modifies a chart, you are not required to activate it, but this
is an easy way to refer to the chart in your code. The following examples modify the chart
type and return the same results, but the first procedure activates the chart sheet and the
second procedure accesses an embedded chart:

Sub ModifyChart1()
ActiveSheet.ChartObjects(“Chart1”).Activate
ActiveChart.Type = xlLine
Chapter 15
ActiveChart.Deselect
End Sub
Sub ModifyChart2()
ActiveSheet.ChartObjects(“Chart1”).Chart.Type = xlLine
End Sub
Keep in mind when creating your macro that if the procedure is written so that it’s dependent
on the chart being activated, an error will occur if the user hasn’t activated the chart. The fol­
lowing example displays several different formats that can be changed. Try executing the
macro with the chart active, as well as with cell A1 selected.

Sub ModifyActiveChart()
With ActiveChart
.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

Notice that run-time error ‘91’: Object variable or With block variable not set occurs when you
execute the ModifyActiveChart procedure and the chart is not selected. To solve this problem,

328