How to create mouse-interactive ChartObject?

[Kelvin Stott]

Hello,

I have created some charts (probability distribution models) as embedded ChartObjects on a worksheet with VBA, and would like to enable events so that I can click and drag the mouse over the chart(s) to change the parameters of the distribution model...

How do I enable events for embedded ChartObjects, so that I can identify the XY coordinates of mouse click-and-drag events (when and where the mouse is clicked and dragged to, with or without shift/ctrl keys)?

I need the X and Y coordinates according to the values on the X and Y axes, rather than pixels, etc.

Thanks for any help or advice to point me in the right direction.

Best regards,
Kelvin

 

[p45cal]

One of these should help:
Using Events with Embedded Charts [Excel 2007 Developer Reference]

Excel 2007 Power Programming with VBA - John Walkenbach - Google Books

 

[Kelvin Stott]

Thanks, I already have JW's huge book, and there's a little bit of info in there (about a page and a half) on enabling events for embedded chartobjects using class modules, but it doesn't explain how to track the XY coordinates of mouse click-and-drag events.

 

[p45cal]

What are you going to be clicking and dragging? Only existing points on the chart?

Depending on your answer to that we could be looking at using GetChartElement, and if it's a series point we can discover that point's coordinates, but when it come to where you're dragging to, if it's empty space on the chart's plot area then you may be looking at converting the pixel location (client coordinates) to the chart's XY values by maybe using the PlotArea's InsideTop, InsideLeft, InsideHeight and InsideWidth properties, comparing with the max and min x and y scales on the axis.

So you've managed to get a new chart class module working, with events, so that an embedded chart responds to events?
Stephen Bullen and Andy Pope are names to Google for and here are some more links:

VBA Techniques | Produce Excel Charts That Look Like What You Had In Mind | InformIT

Get mouse pointer position in chart coordinates

Interactive Chart in VBA using Mouse Move Event | Excel & VBA – Databison

 

[Kelvin Stott]

Actually I only want to click and drag empty spaces in the chart: no object ID required. Depending on the start and end coordinates, a different distribution parameter will change by a certain amount. I will take the initial XY coordinates (based on the XY axes) and use select case to determine which parameter to change based on the area/segment that is clicked within the chart, then calculate how much to change it based on the mouse drag direction and distance (with or without shift/control).

Does that make sense?

PS. Naturally this also means I want to disable chart selection, otherwise the chart object itself will move when I click and drag the mouse on it!

 

[p45cal]

Stolen and adapted from Jon Peltier:

Public WithEvents myChartClass As Chart
Private Sub myChartClass_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim Elemen As Long, Ar1 As Long, Ar2 As Long
Set xxx = myChartClass
Set zzz = xxx.PlotArea
xxx.GetChartElement x, y, Elemen, Ar1, Ar2
'If Elemen = xlPlotArea Then
Stop
X1 = x * 75 / ActiveWindow.Zoom
Y1 = y * 75 / ActiveWindow.Zoom
With zzz
  PlotArea_InsideLeft = .InsideLeft + xxx.ChartArea.Left
  PlotArea_InsideTop = .InsideTop + xxx.ChartArea.Top
  PlotArea_InsideWidth = .InsideWidth
  PlotArea_InsideHeight = .InsideHeight

  With xxx.Axes(xlCategory)
    AxisCategory_MinimumScale = .MinimumScale
    AxisCategory_MaximumScale = .MaximumScale
    AxisCategory_Reverse = .ReversePlotOrder
  End With
  With xxx.Axes(xlValue)
    AxisValue_MinimumScale = .MinimumScale
    AxisValue_MaximumScale = .MaximumScale
    AxisValue_Reverse = .ReversePlotOrder
  End With
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 If
End Sub

You don't really need the GetChartElement parts, I was only experimenting.

PS. Naturally this also means I want to disable chart selection, otherwise the chart object itself will move when I click and drag the mouse on it!

How do you intend to do this?

 

[Kelvin Stott]

Wow, thank you so much! Will take a while to adapt and try this out, I'll let you know.

To disable the chart I use the line:

Set ActiveChart = Nothing

I just need to make sure it's compatible with the above code...

 

[Kelvin Stott]

I have been playing around with the above code, but getting confused between different embedded charts, as well as class vs standard modules.

Then I had a thought:

Is it possible to get the mouse coordinates/movements without using a class module and mousedown event, by assigning a simple macro to my embedded chart so that the macro is activated to record the mouse coordinates/movements when I click on the chart?

This would certainly make coding much simpler, if someone could please suggest what command I need in my macro to track the mouse...

 

[p45cal]

Is it possible to get the mouse coordinates/movements without using a class module and mousedown event, by assigning a simple macro to my embedded chart so that the macro is activated to record the mouse coordinates/movements when I click on the chart?

This would certainly make coding much simpler, if someone could please suggest what command I need in my macro to track the mouse...

Nothing springs to mind; you might be able to do something with API calls (not something I've used a lot), and anyway, I doubt it would be simple.

Someone else may have better suggestions. Many potential responders won't even look at a thread with more than 3 or 4 responses, so why don't you create a new thread with your idea and include a link in it back to this thread for context?

 

[Kelvin Stott]

Thanks for the tip, I will re-post.