MouseDown on Excel Chart

I have a XY Scatter chart as an object on an Excel sheet. I want to allow the user to click anywhere on the chart, upon which I want to call a procedure (sub). The procedure should be able to receive the co-ordinates relative in the chart's axis scales. How do I do this?

I have already got thus far:
a. The Chart_MouseDown seems to work okay, only on a chartsheet, not on a chart object on a worksheet. (For a worksheet object, I could not find a chart_mousedown event handler)

b. I can get the mouse position, but in the form of the location in the window. How do I transform this to co-ordinates in the XY frame of the chart?

Who is Participating?
criConnect With a Mentor Commented:
What are you actually want to accomplish ? Perhaps a neat work-around can be devised, as this one:
stochasticAuthor Commented:
Ten days, not one response! Hey, that's uncharacteristic. What are all the E's doing?

Here, I quintuple the ante. Can do an encore if necessary. C'mon folks, I need serious help here.

- stochastic
stochasticAuthor Commented:
Sorry, it allowed me to increase upto 300 only (I tried 500 first)
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Regarding a)

- Insert|Class Module
- Rename it EventClassModule using the Property window
- Paste this code:
Public WithEvents myChartClass As Chart

Then in any regular VBA module, paste this initializing code:

Dim myClassModule As New EventClassModule

Sub InitializeChart()
    Set myClassModule.myChartClass = Worksheets(1).ChartObjects(1).Chart '<Adapt
End Sub

Regarding b), well, this is anoter story. Place this preliminary 'code' in the new class module:

Private Sub myChartClass_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
  With myChartClass
    MsgBox "Clicked at x=" & X & " 'cc', y=" & Y & " 'cc'"
    With .ChartArea
      MsgBox "ChartArea: W= " & .Width & " pts, H=" & .Height & " pts, etc., etc."
      MsgBox "BUT: How to make a coordinate transformation without scale or reference point ?"
    End With
  End With
End Sub

- Looks like you have to run the initializing Sub again if you bomb it, Reset/Compile does not seem to be enough.
- As mentioned above, I have no clue (yet ?) how to make the necessary coordinate transformation, if you have some clue how to return some additional information like a width in client coordinates I can finish this. Otherwise I do only see the clumsy method of having to select more or less precisely the TopLeft and BottomRight corners of the plot area or similar before doing whatever you want to have done.

stochasticAuthor Commented:
thanks cri,
I will look at this and get back with more.

- stochastic
Should my directions be too shorthand, see Excel VBA Help under 'Event Embedded Chart' (w/o quotes).
stochasticAuthor Commented:

just closing this long-standing question. Your comments were helpful, though I need to get deeper into this to achieve exactly what I need.

thanks anyways.
- stochastic
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.