How to Add Shapes to Charts?

jameskel

New Member
Joined
Sep 17, 2002
Messages
2
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
You may be able to get the effect you want by adding data labels and using a fancy font. Here is an example:

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,609
Members
452,785
Latest member
3110vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top