How to Add Shapes to Charts?

[jameskel]

I would like to programmicly add a line or a dot to an exising Excel Chart.

I would like to add a Line or dot above or below a datapoint.

I can use Shapes.addline(x1,y1,x2,y2) but how do I convert the data's y-values and x-values into the chart's coordinates which I think uses "points" not twips?

How do I convert the data to match Excel's coordinate system?
The containers that I found accessible:
Screen Resolution
Application.Height, etc.
Application.UseableHeight, etc.
ActiveWindow.Height, etc.
ActiveWindow.Height, etc.
ChartArea.Height, etc.
PlotArea.Height, etc.
PlotArea.UseableHeight, etc.

are subject to change with regards to Paper Size, Orientation, Margins, Zoom, WindowState, and PrintedChartSize

I noticed that the Macro Recorder converts the Mouse X,Y coordinates. How do I access this coordinate system?

How do I add a shape at a precise position?
Jim

 

[Andrew Poulsom]

You may be able to get the effect you want by adding data labels and using a fancy font. Here is an example:

Sub Test()
    Dim Ser As Series
    Dim i As Integer
    On Error GoTo NoChartActive
    Set Ser = ActiveChart.SeriesCollection(1)
    Application.ScreenUpdating = False
'   Set data labels
    For i = 1 To Ser.Points.Count
        With Ser.Points(i)
            .HasDataLabel = True
            With .DataLabel
                .Text = Chr(159)
                .Position = xlLabelPositionAbove
                .Font.Name = "Wingdings"
            End With
        End With
    Next i
    Application.ScreenUpdating = False
    Exit Sub
NoChartActive:
End Sub

Activate your chart and run the code.

This places Wingdings character 159 (a bold dot) above the data points. Characters 158 and 160 give normal dots. Symbol characters 189 and 190 give vertical and horizontal lines respectively. Lots of alternatives are available.

You may need to adjust the maximum on the y axis scale.