Positioning labels within chart

[EirikDaude]

I have a spreadsheet containing two charts, in which I want to add some textboxes next to one of the series' points, based on values in a table.

I have created two procedures for this, each with its own pros and cons:

Sub add_comments(apply_to As Series, source_range As Range)
  Dim i As Long
  Dim c As Range
  
  If source_range.Count > apply_to.Points.Count Then
    Set source_range = source_range.Resize(apply_to.Points.Count, 1)
  End If
  
  i = 1
  For Each c In source_range
    If Not IsError(c) And i <= apply_to.Points.Count Then
      If Len(c.Text) <> 0 Then
        apply_to.Points(i).HasDataLabel = True
        apply_to.Points(i).DataLabel.Text = c.Value2
        apply_to.Points(i).DataLabel.Format.AutoShapeType = msoShapeRectangularCallout
        With apply_to.Points(i).DataLabel.Format.Line
          .Visible = msoTrue
          .ForeColor.RGB = RGB(0, 0, 0)
        End With
        apply_to.Points(i).DataLabel.Position = xlLabelPositionAbove
      Else
        If apply_to.Points(i).HasDataLabel Then
          apply_to.Points(i).DataLabel.Delete
        End If
      End If
    End If
    i = i + 1
  Next c
End Sub

The above code uses labels, which is pretty ideal, except I can't reposition the labels and it can get a bit ugly when they overlap.

Sub alternative_comments(apply_to As Series, source_range As Range)
  Dim c As Range
  Dim i As Long
  
  If source_range.Count > apply_to.Points.Count Then
    Set source_range = source_range.Resize(apply_to.Points.Count, 1)
  End If
  
  i = 1
  For Each c In source_range
    If Not IsError(c) And i <= apply_to.Points.Count Then
      If Len(c.Text) <> 0 Then
        With SPC_01.Shapes.AddLabel(msoTextOrientationHorizontal, 100, 100, 10, 10)
          .TextFrame2.TextRange.Characters.Text = c.Text
          With .Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(0, 0, 0)
          End With
          .Top = apply_to.Points(i).Top + .Height
          .Left = apply_to.Points(i).Left + .Width
          
          Debug.Print apply_to.Points(i).Top & " - " & .Top
          Debug.Print apply_to.Points(i).Left & " - " & .Left
        End With
      End If
    End If
    i = i + 1
  Next c
End Sub

The other solution uses textboxes, which is great for moving around and resizing, but they do not position themselves as I expect them to (they are supposed to be in the same position as the labels, roughly), nor do they automatically scale to fit the text. Additionally it has failed to add a box for the last test value I put into the spreadsheet, unknown for what reason

13.03.jpg

Can any of you guys give some input on what some good solutions for automatically adding comments to datapoints in a series is, possibly by solving some of the issues I have described above?

[werafa]

I can't comment with any great prescience, however:

datalables have a .position, .verticalallignment and .horizontalallignment property
https://msdn.microsoft.com/en-us/lib.../ff196984.aspx gives the basics

ps, this is one of the very few times that I've found msdn info useful

Werafa