add labels to line chart
 

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).ApplyDat aLabels
ActiveChart.SeriesCollection(6).Points(x).DataLabe l.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

add labels to line chart
 

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

add labels to line chart
 

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
_______


"Steve Mackay" wrote in message
...
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).ApplyDat aLabels
ActiveChart.SeriesCollection(6).Points(x).DataLabe l.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

add labels to line chart
 

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

add labels to line chart
 

On Apr 9, 5:26 pm, "Jon Peltier"
wrote:
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
_______

"Steve Mackay" wrote in message

...

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).ApplyDat aLabels
ActiveChart.SeriesCollection(6).Points(x).DataLabe l.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

Thanks, John. Really appreciate the help.

add labels to line chart
 

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

add labels to line chart
 

Insert one line like so:

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

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


"Steve Mackay" wrote in message
...

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

add labels to line chart
 

Replace this

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

with this:

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

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



"Steve Mackay" wrote in message
...
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