Positioning Labels Inside a Chart

Question

I have a spreadsheet containing two charts in which I want to add several text fields next to one of the points in the series based on the values ​​in the table.

I created two procedures for this, each of which has its 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 code above uses shortcuts that are pretty good, except that I can't move the labels, and they can get a little 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 

Another solution uses text fields that are great for moving and resizing but don't automatically scale to fit the text, and I can't find a reasonable way to do this.

As you can see, I adhere to both approaches, although I believe that the disadvantages of using labels are somewhat less serious than using text fields. However, I am wondering if any of you can tell me which is the best approach for automatically adding comments to datapoints from a series? Am I on the right track?

I also posted this question on the VBAExpress forums if any of you want to see the whole book.

Answer 1

For an approach with a text field, you can configure it to automate using this:

 .TextFrame2.AutoSize = msoAutoSizeShapeToFitText 

Then there are two options for wrapping text that will change the look. You can either set the text to wrap:

 .TextFrame2.WordWrap = True 

This will not change the width of the text box, which will display the text box vertically, as described above.

Or you can configure it to be absent:

 .TextFrame2.WordWrap = False 

This will output it horizontally until a line break occurs.

Thus, you can set the width of the text field as desired and enable wrapping, or add explicit line breaks (Alt + Enter) to the source text and disable packaging.