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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,224,814
Messages
6,181,120
Members
453,021
Latest member
Justyna P

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