Returning Coordinates of Items in a Chart

[Dendrinos2]

Project: I have multiple charts with lots of data labels... The issue is when I simply add a leading line to connect the data label to the point on the line chart the leading line extends from the right side of the label which is creating overlap issues and looks bad. I need the line to come out of the bottom of every data label.

The solution I found to this was to change the data label shape to a "call out" this allows me to manually control the placement of the leading line.

The issue: I have a fairly large macro that basically creates a bunch of charts based on different data using loops. The issue is I am having trouble telling the vba where to place the lines from my data labels on the charts. I have no issues telling it where to position the data label itself just the line.

ActiveChart.FullSeriesCollection(1).Points(1).DataLabel.Select 'This is the data label
Selection.Format.Adjustments.Item(1) =
Selection.Format.Adjustments.Item(2) =
Selection.Format.Adjustments.Item(3) =
Selection.Format.Adjustments.Item(4) =

1,2,3,4 seem to be the x,y coordinates of the end points I just cant figure out how to return their coordinates.


If anyone can provide a macro that returns the coordinates of these points I would be forever grateful. I can then use those coordinates to tell the vba to position the line to those positions (currently the vba moves the line which is why I need to know the coordinates to move it back)

Attached is a sample spreadsheet.

Thanks.