Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add labels to line chart
Sorry, forgot to say that I am using Excel 2007, if that makes a difference. Thanks steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to change position of chart labels on line chart | Charts and Charting in Excel | |||
labels in line chart | Charts and Charting in Excel | |||
Min, Max Value Labels in Line Chart | Charts and Charting in Excel | |||
Sub-Categories for X-axis labels, line on bar chart? | Charts and Charting in Excel | |||
Can I link timeline labels to a line chart? | Charts and Charting in Excel |