Setting position of AutoShape Item

[specs]

Hi there,

Is there any way to set the position of the yellow point of the autoshape in the picture. So that it will always be at the position of the last point of the trend chart, which is the blue dot. Maybe any1 knows how to obtain the axis position of the last blue dot in my chart, that would slove my problem too.

As far as i know, i can only set the position of the yellow dot by these two lines, the numbers are controling the x and y axis.
Selection.ShapeRange.Adjustments.Item(1) = 0.9613
Selection.ShapeRange.Adjustments.Item(2) = -0.3333

 

[BrianB]

Had a poke around.
Doesn't look like it is possible directly. Shapes are handled on screen using Top/Left/Height & Width properties. There does not seem to be any way of getting the co-ordinates of a point on a graph.

The autoshape "box" itself does have these properties, however the callout "yellow spot" seems to be relative to the callout base. Here is something to play with. It uses the co-ordinates of a cell to move the autoshape around. In this case perhaps a suitable one underneath the chart.

Sub test()
    Dim MyCell As Range
    Dim MyShape As ShapeRange
    '----------------------------------------------
    'Set MyCell = ActiveCell ' alternative test
    Set MyCell = ActiveSheet.Range("a4")
    Set MyShape = ActiveSheet.Shapes.Range("AutoShape 1")
    With MyShape
        .Top = MyCell.Top
        .Left = MyCell.Left
        .ZOrder msoBringToFront
    End With
End Sub

 

[Andy Pope]

Hi,

Rather than trying to calculate the point on the chart and then converting that to a distance the callout shape can use maybe a combination of these 2 work arounds will give you the same result.
Label last point
Connector lines

 

[specs]

Thanks guys for your replies.

Just a slight question on BrianB's code suggestion:

Set MyCell = ActiveSheet.Range("a4")

so what data should be in Range("a4") and to be set into MyCell?
is the Range("a4") in Sheet1 for example?

 

[BrianB]

so what data should be in Range("a4")

Any data. In this case the cell is just used as a marker to set the position of the shape (which uses the screen co-ordinates of the cell). The idea here is to choose a suitable cell on the "grid" rather than using trial and error.

Top left of the window has co-ordinates 0,0.