Get mouse coordinates in chart

[Jacc]

I'm trying to make a lookup table from a efficiency chart found in a pdf. Cutting the chart and pasting is as background in a Excel chart is a good start, so far so good.

Now I want to use VBA to get mouse coordinates (and chart values) from the chart. It turned out to be very difficult.
Either my calculations are way off or Excel is playing trick on me cause I just can't get the values correct, no way, no how....

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim wLA As Double   'width of Left Axis
Dim wRA As Double 'width of Right Axis
Dim wPA As Double   'width of Plot Area
Dim lPA As Double   'left placement
Dim NoOfPointsFromLeftOnPlotArea As Double
Dim ActualWidthOfPlotArea As Double
Dim PercentOfPlotAreaWidth As Double
Dim XValueOnPlot As Double


wLA = Me.Axes(xlValue).Width
Debug.Print "wLA " & wLA

lPA = Me.PlotArea.Left
Debug.Print "lPA " & lPA

NoOfPointsFromLeftOnPlotArea = x - lPA - wLA
Debug.Print "NoOfPointsFromLeftOnPlotArea " & NoOfPointsFromLeftOnPlotArea


wRA = Me.Axes(xlValue, xlSecondary).Width
Debug.Print "wRA " & wRA

wPA = Me.PlotArea.Width
Debug.Print "wPA " & wPA


ActualWidthOfPlotArea = wPA - wRA - wLA
Debug.Print "ActualWidthOfPlotArea " & ActualWidthOfPlotArea

PercentOfPlotAreaWidth = NoOfPointsFromLeftOnPlotArea / ActualWidthOfPlotArea

XValueOnPlot = PercentOfPlotAreaWidth * 1000
MsgBox XValueOnPlot


End Sub

[Andy Pope]

There is a addin to replicate the old graphical goal seek functionality. Perhaps you can make use of it
https://blogs.office.com/2009/11/02/...ints-on-chart/

[Jacc]

There is a addin to replicate the old graphical goal seek functionality. Perhaps you can make use of it
https://blogs.office.com/2009/11/02/...ints-on-chart/

Thanks for the tip Andy!
However, I would expect a man with your resumé to solve this problem in a second. Only a few lines of code.
Or do you have previous experience of this and know that is almost impossible?

[AlphaFrog]

NoOfPointsFromLeftOnPlotArea = x - lPA - wLA

I don't have a solution for you, but just to let you know that X and Y values are not coordinates on the plot area. They are coordinates in the workbook window where (0,0) is the upper left corner just below the formula bar.

[Jacc]

I know, that is why I subtract lPA (Left property of plot area) and wLA (Width property of left axis).
Still, the numbers don't add upp AT ALL and I can't figure it out what is going on.

[Jacc]

If I set the Plotarea Left property to 100, the plot area moves to that position just fine.
However if I click around with the mouse pointer until I get x=100 then the mouse pointer is waaayy to the left of the plotarea. It's like they operate with different units or something. Is one pixels and the other points or something like that?

[Andy Pope]

It's a nightmare due to the different coordinate systems used.

Regarding the addin. I had assumed that it was doing the adjustment graphical, as per the old way. But having just taken a look I see it uses controls on a userform.

[Jacc]

I believe you when you say it's a nigthmare. What I find strange is that no one has ever solved this problem before, to me it seems like a usable function.

[Andy Pope]

The simplest way would be to take a couple of sighting clicks first.
If you click top left and bottom right of plot area you can then use mouse positions only

Or maybe this approach would work.
http://www.clearlyandsimply.com/clea...el-charts.html

[Jacc]

Thanks again. I think I'll leave this for now and get back to work, it's not critical.
I would still be impressed and happy if someone suddenly posted a solution so I just leave this thread Unsolved.