Data Label Object and the Data Labels Collection Example

This example adds data labels to all the points on the chart, using the column to the left of the X values range:

Sub AddDataLabels() Dim oSer As Series Dim vaSplits As Variant Dim oXRng As Range Dim oLblRng As Range Dim oLbl As DataLabel Dim i As Integer

'Loop through all the series in the chart

For Each oSer In ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection 'Get the series formula and split it into its 'constituent parts (Name, X range, Y range, order) vaSplits = Split(oSer.Formula, ",")

'Get the X range

Set oXRng = Range(vaSplits(LBound(vaSplits) + 1))

'Get the column to the left of the X range Set oLblRng = oXRng.Offset(0, -1)

'Show data labels for the series oSer.ApplyDataLabels

'Loop through the points For i = 1 To oSer.Points.Count

'Get the DataLabel object

Set oLbl = oSer.Points(i).DataLabel oLbl.Caption = oLblRng.Cells(i)

Next Next End Sub

0 0

Post a comment