Microsoft Office Tutorials and References
In Depth Information
Determining Whether a Chart Is Activated
As far as I can tell, the only way to deactivate a chart by using VBA is to select something other
than the chart. For an embedded chart, you can use the RangeSelection property of the
ActiveWindow object to deactivate the chart and select the range that was selected before the
chart was activated:
ActiveWindow.RangeSelection.Select
To deactivate a chart on a chart sheet, just write code that selects a different sheet.
Determining Whether a Chart Is Activated
A common type of macro performs some manipulations on the active chart (the chart selected
by a user). For example, a macro might change the chart’s type, apply a style, add data labels, or
export the chart to a graphics file.
The question is, how can your VBA code determine whether the user has actually selected a
chart? By selecting a chart, I mean either activating a chart sheet or activating an embedded
chart by clicking it. Your first inclination might be to check the TypeName property of the
Selection , as in this expression:
TypeName(Selection) = “Chart”
In fact, this expression never evaluates to True . When a chart is activated, the actual selection
will be an object within the Chart object. For example, the selection might be a Series object,
a ChartTitle object, a Legend object, a PlotArea object, and so on.
The solution is to determine whether ActiveChart is Nothing . If so, then a chart isn’t
active. The following code checks to ensure that a chart is active. If not, the user sees a message,
and the procedure ends:
If ActiveChart Is Nothing Then
MsgBox “Select a chart.”
Exit Sub
Else
‘other code goes here
End If
You may find it convenient to use a VBA function procedure to determine whether a chart is
activated. The ChartIsSelected function, which follows, returns True if a chart sheet is active or
if an embedded chart is activated, but returns False if a chart isn’t activated:
Private Function ChartIsSelected() As Boolean
ChartIsSelected = Not ActiveChart Is Nothing
End Function
 
Search JabSto ::




Custom Search