I have an Excel scatterplot with a number of 2-point series. I am trying to set the position of each DataLabel in VBA, but its behavior is unpredictable. Here is the result, with each label text showing the (
I would expect every label with
And here is the code:
.Left, .Top
) value that was set: I would expect every label with
.Left = 100
to be positioned at the same X position on the chart. But the first one is positioned horizontally in the middle, and the next four are stuck against the left side of the chart. Likewise, the .Top
position is not placing them consistently. For example, (100, 250) is much higher vertically than (300, 250).And here is the code:
VBA Code:
Sub PositionDataLabels()
For i = 1 To 10
ActiveChart.FullSeriesCollection(i).Select
ActiveChart.SetElement (msoElementDataLabelCallout)
ActiveChart.FullSeriesCollection(i).Points(2).DataLabel.Select
Selection.Delete ' Delete second DataLabel; only want one per series
ActiveChart.FullSeriesCollection(i).Points(1).DataLabel.Select
Selection.Format.TextFrame2.TextRange.Font.Size = 12
If i > 5 Then
Selection.Left = 300
Selection.Top = 100 + 50 * (i - 5)
Selection.Format.TextFrame2.TextRange.Characters.Text = CStr(Selection.Left) & ", " & CStr(Selection.Top)
Else
Selection.Left = 100
Selection.Top = 100 + 50 * i
Selection.Format.TextFrame2.TextRange.Characters.Text = CStr(Selection.Left) & ", " & CStr(Selection.Top)
End If
Next
End Sub