VBA to set absolute position of chart DataLabel

dbooksta

New Member
Joined
Jun 29, 2013
Messages
8
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 (.Left, .Top) value that was set: Excel chart with DataLabels

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,223,883
Messages
6,175,167
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top