hi i'm trying to write a macro that;
when i click on a point on a scatter plot (but it has to be clicked twice
otherwise the series is selected, rather than the point), i need to read the
cell addresses for that point
all help greatly appreciated
SD

See John Walkenback at:

http://j-walk.com/ss/excel/odd/odd15.htm

As per John Walkenbauch at the above reference, it is not possible to obtain
the source range for a chart series using VBA. John describes a technique of
parsing the chart Series Formula to obtain the source address.

In short, the information cannot be obtained using VBA by itself. However,
if you already know the source range and you only want to return the source
address of an individual point within the source range by clicking the point,
you can use the GetChartElement method to return the index value of the point
and use that as an offset within the known source range. The following
example assumes that the source range is a named range called "ChartVals" and
that the chart is a chart sheet as opposed to an embedded chart.

Paste to the chart sheet code module:

Private Sub Chart_MouseDown(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ID As Long, a As Long, b As Long
With Me
.GetChartElement x, y, ID, a, b
MsgBox "Point index value = " & b & vbCr & _
"Point source address = " & Range("ChartVals")(b).Address
End With
End Sub

Note: If the chart is embedded then you need to resort to a Class module
techinique.

Regards,
Greg


"SD" wrote:


hi i'm trying to write a macro that;
when i click on a point on a scatter plot (but it has to be clicked twice
otherwise the series is selected, rather than the point), i need to read the
cell addresses for that point
all help greatly appreciated
SD