add labels to line chart

[Steve Mackay]

Hi All

I have a line chart with 11 series. I want to add a data label to the
last point only for each of the series, showing the value and series
name with the legend key. I would like to have code that will cycle
through each of these series and add that information (note, this is
probably simple, but I am a beginner with VBA)
Here is some code that I recorded, that works for one of the series
(Series 6):

Sub Macro3()
x = Sheets("Data").Cells(1, 1).Value
Sheets("Chart").Select
ActiveChart.SeriesCollection(6).Points(x).ApplyDataLabels
ActiveChart.SeriesCollection(6).Points(x).DataLabel.Select
Selection.ShowSeriesName = -1
Selection.ShowLegendKey = -1
ActiveChart.ChartArea.Select
End Sub

Note: I made the "point" a variable because the chart data is
dynamic. This code refers to a cell (Sheet "Data" cell A1) that
counts the number of rows of data (will expand and contract depending
on dates I choose). That way, I am always getting just the last
point. This seems to work fine, I just need code to cycle through all
of the series and add the same label.

Many thanks for your help
Steve

 

[Steve Mackay]

Sorry, forgot to say that I am using Excel 2007, if that makes a
difference.
Thanks
steve

 

[Jon Peltier]

If you look, you'll see

SeriesCollection(6)

in your code. This means the code is only applying a label to the 6th
series. Introduce a variable that loops through all series numbers, as I've
demonstrated in the code below. Select the chart and run this code:

Sub Macro4()
Dim iSrs As Long
Dim iPts As Long
For iSrs = 1 To ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(iSrs)
iPts = .Points.Count
With .Points(iPts)
.ApplyDataLabels
.DataLabel.ShowSeriesName = -1
.DataLabel.ShowLegendKey = -1
End With
End With
Next
End Sub

Note: I changed your variable 'x' to iPts for clarity. I generalized it to
work on the active chart, and to figure out the number of points without
reading it from outside the chart. I also used some With/End With loops.

- Jon

 

[Steve Mackay]

Sorry to keep posting replies to my own request. I found something
close to what I want, but it only gives me the name of my series. I
would also like to have the value of the point too. Any ideas?

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
Sheets("Chart").Select
For Each mySrs In ActiveChart.SeriesCollection
With mySrs
nPts = .Points.Count
mySrs.Points(nPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, LegendKey:=True
mySrs.Points(nPts).DataLabel.Text = mySrs.Name

End With
Next
End Sub

Thanks
Steve

 

[Steve Mackay]

If you look, you'll see

SeriesCollection(6)

in your code. This means the code is only applying a label to the 6th
series. Introduce a variable that loops through all series numbers, as I've
demonstrated in the code below. Select the chart and run this code:

Sub Macro4()
Dim iSrs As Long
Dim iPts As Long
For iSrs = 1 To ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(iSrs)
iPts = .Points.Count
With .Points(iPts)
.ApplyDataLabels
.DataLabel.ShowSeriesName = -1
.DataLabel.ShowLegendKey = -1
End With
End With
Next
End Sub

Note: I changed your variable 'x' to iPts for clarity. I generalized it to
work on the active chart, and to figure out the number of points without
reading it from outside the chart. I also used some With/End With loops.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

Thanks, John. Really appreciate the help.

 

[Steve Mackay]

John,

This works well, thanks. Just one more question. If I already have
labels, then add more data and repeat the macro, I'll have two labels
for each series. Is there a way to first cycle through and delete all
existing labels?

Thanks
Steve

 

[Jon Peltier]

Insert one line like so:

With ActiveChart.SeriesCollection(iSrs)
.HasDataLabels = False
iPts = .Points.Count

- Jon

 

[Jon Peltier]

Replace this

mySrs.Points(nPts).DataLabel.Text = mySrs.Name

with this:

mySrs.Points(nPts).DataLabel.ShowSeriesName = True

- Jon