PDA

View Full Version : Positioning labels within chart



EirikDaude
03-13-2017, 07:50 AM
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 :doh:

18616

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
03-21-2017, 04:17 AM
I can't comment with any great prescience, however:

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

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

Werafa