Attaching a Textbox to a point or line on a chart in Excel/VBA

Question

I was wondering how to attach a textbox to a point or line in an Excel chart for the macro I am working on. I have been using the .AddTextbox method such as

.Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 250, 100, 15) _
      .TextFrame.Characters.Text = "Temperature"

But I have to then manually drag the textbox over the line on the chart it is representing as the orientation is of the chart not the line. Is there a way to convert the line/point to a chart orientation which I could use as a variable? or another way? Possibly using the datalabel function, though I want to be able to customize one of the axis locations. Thanks

Answer 1

To solve your question you need to get the left & top position of two objects:

1. chart itself, which position is set in relation to top-left corner of sheet range area
2. point in series which position is set in relation to top-left corner of chart

Combination of both result with the following code (fixed parameters-required changes to your situation, could be more dynamic with loop)

Sub Add_Text_to_point()

    Dim tmpCHR As ChartObject
    Set tmpCHR = Sheet1.ChartObjects(1) 'put index of your chartobject here

    'for first serie, for point 2nd here
    '(change accordingly to what you need)
    With tmpCHR.Chart.SeriesCollection(1).Points(2)

        Sheet1.Shapes.AddTextbox(msoTextOrientationHorizontal, _
                .Left + tmpCHR.Left, _
                .Top + tmpCHR.Top, _
                100, 15) _
                .TextFrame.Characters.Text = "Temperature"
    End With

End Sub

After result presenting the picture below.