Data LabelsVBA Name Data Labels and Data Label

A series comprises data points. Some or all of the data points in a series may have data labels. The data labels may show the X (category) value, Y (value) value, or some custom value that you specify. A custom value can be literal or a cell reference. You can use VBA to

■ Specify that all points of all series in a chart show data labels of a particular kind (either value or category) or show no data labels:

ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone

■ Specify that a particular series show the values (Y) as data labels:

With ActiveChart.SeriesCollection("Xdata") .HasDataLabels=True

.ApplyDataLabels Type:= xlDataLabelsShowValue End With

■ Specify that a particular point on a series show a literal in its data label

With ActiveChart.SeriesCollection("Xdata").Points(1) .HasDataLabel=True .DataLabel.Text="MyLabel" End With

■ Put a formula (single cell reference) in the data label for a particular point on a series (note that you need to use the RC style in the formula):

With ActiveChart.SeriesCollection("Xdata").Points(1) .HasDataLabel=True .DataLabel.Text="=Sheet1!R1C1" End With

You can also use code to specify the location and orientation of data labels with respect to the data points, similar to how you do it in the user interface.

Try the following macro to set the data labels for series Xdata:

Sub DataLabelDemo()

With Charts("Chart1").SeriesCollection("Xdata") .HasDataLabels = True

.ApplyDataLabels Type:=xlDataLabelsShowValue With .DataLabels

.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Position = xlLabelPositionAbove .Orientation = xlUpward End With End With End Sub

Notice that the properties and methods have names appropriate to their functions.

0 0

Post a comment