How to create mouse-interactive ChartObject?

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
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
 

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

Active Member
Joined
Oct 26, 2010
Messages
325
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!
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Stolen and adapted from Jon Peltier:
Code:
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?
 
Last edited:

Kelvin Stott

Active Member
Joined
Oct 26, 2010
Messages
325
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

Active Member
Joined
Oct 26, 2010
Messages
325
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...
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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?
 

Forum statistics

Threads
1,082,373
Messages
5,365,074
Members
400,822
Latest member
Aldebaran13

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top