Direct labelling excel graphs using VBA - same color for line and label

[celiacaciones]

I have attached a VBA code and an Excel file with the graph I'm trying to re-style.
That piece of code was very useful to make direct labeling over a line graph in excel. A suggestion from PolicyViz website.
However I would like to go further and apply, automatically, the same color code (actually same format/style, but at least the color) from the line to the label to boost the visualization experience. Especially if your graphs as many series (lines) this will be too burdensome and inefficient to do on a series by series basis, manually.
I'm not even in control of the colors excel is choosing for the lines ....
It would be ideal to embed this styling in the macro, so all could be done in one go, and fully automatically.
Can someone help?

 

[Hui]

Celiacaciones

Firstly, Welcome to the Chandoo.org Forums

I would tackle this issue totally differently to what you proposed

I would first add another 10 dummy ranges as shown in the picture below
These will all have the value =NA() except the last point
Then add those new 10 series to the chart
Then add a Data Label to each new series
Change the Data Label to show Series Name instead of Value
Change the Format to match the corresponding line
If you don't like the look of those 10 lines of errors shift them down or to the side out of the way
Enjoy

This is totally dynamic as long as you insert new data before the last point


see attached file

 

[celiacaciones]

Hi Hui,
Thank you for your reply. I do like your approach as it doesn't require VBA, which I'm not an expert at all.
Actually I did try that approach before, with similar results as you (I did not use the NA filling, just left it blank, makes no difference). You can even choose position 2014 or 2013 if you prefer the label to be more inside the graph.

The part I don't like about this approach, is that you still have to manually do a lot of stuff (add the label, change to series name, format the labels to match the series - find out which colour is that exactly ...). If it was only one or two graphs, once in a while, fine. But I'm trying to set up a regular flow of statistics with several graphs. So I was hoping I could automatize more the process by choosing the colors upfront and having them fixed for both lines and labels (assuming the number of series would remain the same) ...

 

[vletm]

@celiacaciones
Here You would find almost everything:
http://www.globaliconnect.com/excel...area-plot-area-chart-axes&catid=79&Itemid=475

 

[Hui]

The following code will add the Series Name to the last point on each series in each chart on the activeworksheet

Sub Add_Trailing_Series_Name()

For x = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(x).Activate
   
  For i = 1 To ActiveChart.FullSeriesCollection.Count
 
  ActiveChart.FullSeriesCollection(i).Select
  ActiveChart.FullSeriesCollection(i).ApplyDataLabels
   
  ActiveChart.FullSeriesCollection(i).DataLabels.Select
  Selection.ShowSeriesName = True
  Selection.ShowValue = False
  ActiveChart.FullSeriesCollection(1).HasLeaderLines = False
   
  np = ActiveChart.FullSeriesCollection(i).Points.Count
  For j = 1 To np - 1
  ActiveChart.FullSeriesCollection(i).Points(j).DataLabel.Delete
  Next j
   
  With ActiveChart.FullSeriesCollection(i).Points(ActiveChart.FullSeriesCollection(i).Points.Count).DataLabel
  .Format.TextFrame2.TextRange.Font.Bold = msoTrue
  .Format.TextFrame2.TextRange.Font.Size = 12
  .Format.TextFrame2.TextRange.Font.Fill.Visible = msoTrue
  .Format.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _
  ActiveChart.FullSeriesCollection(i).Format.Line.ForeColor.RGB
  End With
 
  Next i
Next x

End Sub

or see the attached file

Please let me know what you think

 

[Han Ki Nam]

>>> use code - tags <<<

Sub LabelLastPoint()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
  With mySrs
    nPts = .Points.Count
    mySrs.Points(nPts).ApplyDataLabels _
       Type:=xlDataLabelsShowValue, _
       AutoText:=True, LegendKey:=False
    mySrs.Points(nPts).DataLabel.Text = mySrs.Name
    mySrs.DataLabels.Font.ColorIndex = mySrs.Border.ColorIndex
  End With
Next
End Sub