Thread: how to add a text box to a chart position it ?

Hi,
How can I with a macro create text boxes and place them in a chart?
TO each textbox there should be an arrow that points towards a certain Y value on the y-axis. I've tried versions of the code below, But I don't know how to link the y-axis vaule with the position of the arrow and text box.
Any suggestions?

Sub Makro1()

'
    ActiveSheet.ChartObjects("Diagram 1").Activate

    Application.CommandBars("Drawing").Visible = True
    ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 89.25, 66.75, 90.75 _
        , 57.75).Select
    Selection.Characters.Text = "anders testar"
   

    ActiveChart.Shapes.AddLine(41.25, 96#, 84#, 96#).Select
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
    Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
    Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
    Selection.ShapeRange.Flip msoFlipHorizontal
    ActiveWindow.Visible = False
    
End Sub

Can you post example chart and describe what you are trying to do.

Can you not use data labels to display text?

This uses an additional xy series to display data labels.
The data label text is linked to cells which contain all the information to be displayed.

If you need leader lines you can add another series to create those.


If you still need to add and position the textboxes via code then this may help.
http://www.andypope.info/vba/Axislabel_textboxes.htm

You need to workout the plotareas dimensions in relation to the axis scale and then calculate the new position of the textbox given a X and or Y value at which to place it.