X,Y coordinates on chart

[lgarcia3]

I have to create a char, that when clicked on will return the point of the click. PLEASE, noticed that I will be clicking anywhere on the plot NO on a SERIES of points. In other words, lets say I have a XY chart with these values:

x= 1,2,3,4,5,6,7,8
y= 2,9,4,5,7,2,4,1

If I do an ALEATORY click somewhere on the plot (NOT ON AN ALREADY PLOTTED SERIES!) I will get the values equals or close to the ones shwon above.
Thanks!

[Jon Peltier]

You can capture a mouse click event, and determine the X,Y coordinates of
the point. This X and Y are not related to the chart axes, but a little
algebra will get you what you need. This X,Y is in the coordinates of the
chart object, which is in pixels from the top left of the chart. Convert
pixels to points, which are used for chart element dimensions. Determine
where the converted X,Y fit within the plot inside area (turning pixels into
a percentage of each axis scale), and then convert from this percentage to
axis units.

This code shows a mouse_down event procedure which captures all of this
information. It only works for an XY chart's value X axis, not the
date-scale or category axis of a line/area/column chart (but these merely
need different algebraic manipulations). Step through it the first couple
times to make sure it does what is expected.

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, _

ByVal X As Long, ByVal Y As Long)

Dim PlotArea_InsideLeft As Double

Dim PlotArea_InsideTop As Double

Dim PlotArea_InsideWidth As Double

Dim PlotArea_InsideHeight As Double

Dim AxisCategory_MinimumScale As Double

Dim AxisCategory_MaximumScale As Double

Dim AxisCategory_Reverse As Boolean

Dim AxisValue_MinimumScale As Double

Dim AxisValue_MaximumScale As Double

Dim AxisValue_Reverse As Boolean

Dim datatemp As Double

Dim Xcoordinate As Double

Dim Ycoordinate As Double

Dim X1 As Double

Dim Y1 As Double



X1 = X * 75 / ActiveWindow.Zoom

Y1 = Y * 75 / ActiveWindow.Zoom



PlotArea_InsideLeft = PlotArea.InsideLeft + ChartArea.Left

PlotArea_InsideTop = PlotArea.InsideTop + ChartArea.Top

PlotArea_InsideWidth = PlotArea.InsideWidth

PlotArea_InsideHeight = PlotArea.InsideHeight



With Axes(xlCategory)

AxisCategory_MinimumScale = .MinimumScale

AxisCategory_MaximumScale = .MaximumScale

AxisCategory_Reverse = .ReversePlotOrder

End With

With Axes(xlValue)

AxisValue_MinimumScale = .MinimumScale

AxisValue_MaximumScale = .MaximumScale

AxisValue_Reverse = .ReversePlotOrder

End With



datatemp = (X1 - PlotArea_InsideLeft) / PlotArea_InsideWidth * _

(AxisCategory_MaximumScale - AxisCategory_MinimumScale)

Xcoordinate = IIf(AxisCategory_Reverse, _

AxisCategory_MaximumScale - datatemp, _

datatemp + AxisCategory_MinimumScale)



datatemp = (Y1 - PlotArea_InsideTop) / PlotArea_InsideHeight * _

(AxisValue_MaximumScale - AxisValue_MinimumScale)

Ycoordinate = IIf(AxisValue_Reverse, _

datatemp + AxisValue_MinimumScale, _

AxisValue_MaximumScale - datatemp)



MsgBox "X = " & Xcoordinate & vbCrLf & "Y = " & Ycoordinate

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"lgarcia3" <[email protected]> wrote in
message news:[email protected]...
>
> I have to create a char, that when clicked on will return the point of
> the click. PLEASE, noticed that I will be clicking anywhere on the plot
> NO on a SERIES of points. In other words, lets say I have a XY chart
> with these values:
>
> x= 1,2,3,4,5,6,7,8
> y= 2,9,4,5,7,2,4,1
>
> If I do an ALEATORY click somewhere on the plot (NOT ON AN ALREADY
> PLOTTED SERIES!) I will get the values equals or close to the ones
> shwon above.
> Thanks!
>
>
> --
> lgarcia3
> ------------------------------------------------------------------------
> lgarcia3's Profile:
> http://www.excelforum.com/member.php...fo&userid=7488
> View this thread: http://www.excelforum.com/showthread...hreadid=513428
>

[lgarcia3]

Jon,
Thanks a million! That is excellent. I was reading your web site. It's very good.
Thanks again!