The Data Label Object

A DataLabel object represents the data label of a chart data point (or trendline). (We discuss the Point object later in the chapter.) Each Series object has a DataLabels collection that contains one DataLabel object for each point in the series. The Data-Labels collection is returned by the DataLabels method, as in:

If ActiveChart.SeriesCollection(l). HasDataLabels Then MsgBox ActiveChart.SeriesCollection(l). DataLabels.Count

End If

Note that if there are no data labels for a given series, then the DataLabels method will generate an error; we should check this first before calling the method, using the HasDataLabels property of the Series object.

The visibility of a data label (not its existence) is governed by the HasDataLabel property of the corresponding Point object (discussed later). Thus, the code:

ActiveChart.SeriesCollection(1).Points(1).HasDataLabel = False suppresses the display of a data label for the first data point in the series.

We can use the ApplyDataLabels method to display or hide data labels and to change the type of labels. The syntax for this method is:

expression .ApplyDataLabels( Type, LegendKey)

where expression can return either a Chart, Point, or Series object. When the method is applied to a Chart object, it affects the data labels for all series in the chart at the same time.

The Type parameter is the data-label type and can be one of the following XlDataLabelsType constants:

Enum XlDataLabelsType xlDataLabelsShowNone = -4142 xlDataLabelsShowValue = 2 xlDataLabelsShowPercent = 3 xlDataLabelsShowLabel = 4 xlDataLabelsShowLabelAndPercent = 5 xlDataLabelsShowBubbleSizes = 6 End Enum

The optional LegendKey parameter can be set to True to show the legend key next to each data point. The default value is False. Figure 21-27 shows data point legends in action.

Macros Excel Data

The properties and methods of the DataLabel object are shown in Table 21-5.

Table 21-5. Members of the DataLabel Object

Application

Font

ReadingOrder

AutoScaleFont

HorizontalAlignment

Select

AutoText

Interior

Shadow

Border

Left

ShowLegendKey

Caption

Name

Text

Characters

NumberFormatLinked

Top

Creator

Orientation

Type

Delete

Parent

VerticalAlignment

Fill

Position

Note that on a trendline (discussed later in this chapter), the DataLabel property returns the text shown with the trendline. This text can be the equation, the R-squared value, or both (if both are showing).

+4 -3

Responses

Post a comment