VBA Series label in line scatterchart in Excel

[test1]

Hi all,

Apologies if this has already been asked before.

I'm working on an Excel file which shows the relative performance through time of bodns (all rebased at 100 at the starting period). The period is beginning of this year to today. This is a chart that I'd like to use in the future which will update based on market data.

I am using a scattechart with straight lines.
For each bond, I have a defined series with dates as x-axis and bond relative performance as y-axis.

I have a lot of bonds and I just would like to have a macro which would, for each series, put the name of the series (say "JPM 2% 2025") on the last data point of that series (say today). I don't need any other label as the scales would be sufficient to see the relative performance.

I started from a code I found on another post here. However, it returns the relative performance value (the y-axis value). I can't find a way to get the Series name.

Could you please help me?

Thanks in advance for your assistance.

Regards,

Stephane

 

[test1]

of course, I forgot to provide you with the code I started from:

Sub LastDataLabels3()
    Dim oChart As ChartObject
    Dim MySeries As Series

ActiveSheet.ChartObjects("Comparison_of_CoCos").Activate

    For Each oChart In ActiveSheet.ChartObjects
        For Each MySeries In oChart.Chart.SeriesCollection
'           Clear ExistingData Labels
            MySeries.ApplyDataLabels (xlDataLabelsShowNone)
          
'           Apply Labels to First point
          
         MySeries.Points(1).ApplyDataLabels
        ActiveChart.MySeries.DataLabels.ShowSeriesName = True
          '.DataLabels.ShowSeriesName = True
         
          

        Next MySeries
    Next oChart
End Sub

This works well until "MySeries.Points(1).ApplyDataLabels" (for good reasons, as I didn't really alter this part of the macro I got on this forum).
But it gets more complicated once I want to have the series name.

Thanks again for your help!

Regards,

Stephane

 

[vletm]

test1 & Stephane
You also missed that sample Excel-file
... without that, many things are challenge to test!

 

[test1]

Hi,

Please see attached an example. I have put 4 bonds, and the macro as is does insert the latest relative price for 26/03/2020 (today). Instead of having this relative price for the last date, I'd like to see instead the series label names (i.e. bond 1, bond 2, bon3 and bond 4).

That's exactly what I can't figure out.

PS: the reason I do not want to simply add a legend, is because in the original file (which is much bigger than the sample) I use check buttons to choose which bonds I want/do not want to show on the chart, out of a much bigger sample. Using a simple legend would result in too many bonds showing in the legend.

Thanks very much for your help,

Best regards,

Stephane

 

[p45cal]

Try:

Sub LastDataLabels3()
Dim oChart As ChartObject
Dim MySeries As Series
 
For Each oChart In ActiveSheet.ChartObjects
  For Each MySeries In oChart.Chart.SeriesCollection
    MySeries.ApplyDataLabels (xlDataLabelsShowNone) 'Clear ExistingData Labels
    MySeries.Points(1).ApplyDataLabels ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False
  Next MySeries
Next oChart
End Sub

 

[test1]

Hi p45cal,

Sorry for the late reply. Your code works perfectly.

Thanks very much for your help, much appreciated!

Best regards,

Stephane