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 colors, or change the font size.

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"

This expression above evaluates to True if a chart sheet is active, but it won't be True if an embedded chart is selected. Rather, when an embedded chart is selected, 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. The ChartlsSelected function, which follows, returns True if a chart sheet is active or if an embedded chart is activated but returns False if a chart is not activated:

Private Function ChartIsSelected() As Boolean

ChartlsSelected = Not ActiveChart Is Nothing End Function

This function determines whether the ActiveChart object is Nothing. If so, a chart is not activated.

0 0

Post a comment