Positioning labels within chart

Question

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 the do not automatically scale to fit the text, and I can't find any sensible way to do that either.

As you can see I am stuck on both approaches, although I feel the downsides for using labels are somewhat less severe than for using textboxes. However, I wonder if any of you can tell me what the best approach is for automatically adding comments to datapoints in a series? Am I on the right track?

I have also posted this question to the VBAExpress forums, if any of you want to have a look at the entire workbook.

I think your best bet would be to first calculate what points will get a data label and then work out the maximum width a data label can have. Might this width be too small you could use the datalabel.top property to change the position of the data label relative to the top of the chart, and place labels aboven one another. — Luuklag, Mar 14 '17 at 07:56
@Luuklag Yeah, it seems like this may be my best option. Figuring out of to prevent them from overlapping is going to be an absolute pain though :-| Thanks a lot for your suggestion, I have made some progress using it already. — eirikdaude, Mar 14 '17 at 11:53

score 0 · Answer 1 · answered Mar 14 '17 at 11:14

0

For the textbox approach, you can set it to autosize using this:

.TextFrame2.AutoSize = msoAutoSizeShapeToFitText

There are then two options for text wrapping that will change the appearance. You can either set the text to wrap:

.TextFrame2.WordWrap = True

This will not change the textbox width, which will string out the textbox vertically as you have above.

Or you can set it to not wrap:

.TextFrame2.WordWrap = False

This will string it out horizontally, until it comes across a line break.

You can therefore either set the textbox width as desired and turn wrapping on, or add explicit line breaks (Alt + Enter) in your source text and turn wrapping off.

answered Mar 14 '17 at 11:14

SteveES

544
5
10

Thanks - as you can see from the image, the textboxes do autosize, strictly speaking, the problem is just that it autosizes in only one dimension! Creating them with a set width and then working from there may be a solution of course. – eirikdaude Mar 14 '17 at 11:51
Have you tried turning off word wrap? Is that an option? – SteveES Mar 14 '17 at 11:57

Positioning labels within chart

1 Answers1