Excel Chart Series pixel location

Question

Need : In Excel, on every update of Data Source of the chart, The current end point of one of the chart series should be highlighted with the small circle Shape.

so, is there any possibility to find the location details of that series, to move circle accordingly.

user3514930 user3514930 · Accepted Answer · 2014-05-21T08:23:57

You can use to create (in a module):

Public NameOval As String

Sub CirclePt()
    ActiveSheet.ChartObjects("Chart 14").Activate
    x = Selection.Left + ActiveChart.PlotArea.Left

    y = Selection.Top + ActiveChart.PlotArea.Top
    ActiveChart.SeriesCollection(1).Points(ActiveChart.SeriesCollection(1).Points.Count).Select
    x = x + Selection.Left
    y = y + Selection.Top

    ActiveSheet.Shapes.AddShape(msoShapeOval, x - 30, y - 30, 60, 60).Select
    Selection.ShapeRange.Fill.Visible = msoFalse
    NameOval = Selection.Name
End Sub

and in the sheet, to move/update the position of the shape:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.ChartObjects("Chart 14").Activate
    x = Selection.Left + ActiveChart.PlotArea.Left

    y = Selection.Top + ActiveChart.PlotArea.Top
    ActiveChart.SeriesCollection(1).Points(ActiveChart.SeriesCollection(1).Points.Count).Select
    x = x + Selection.Left
    y = y + Selection.Top

    ActiveSheet.Shapes.Range(Array(NameOval)).Select
    Selection.Top = y - 30
    Selection.Left = x - 30
End Sub

Excel Chart Series pixel location

1 Answers