Translating Mouse coordinates to Excel Chart position 1

[CautionMP]

Hello,
So I building a custom graphing engine in Excel (2000 SR-1/2003). I'm working with a single chart sheet and want to create a vertical line ([tt]shpTracker[/tt]) that tracks the mouse pointer in the [tt]Plot Area[/tt] of the chart. Eventually the line position will update a 'dashboard' with statistics that correspond to the position of the vertical line within the [tt]Plot Area[/tt] (phase 2).

Working with the [tt]MouseMove()[/tt] event I can get [tt]shpTracker[/tt] to respond to the mouse movement and track across the chart, but I'm struggling to get [tt]shpTracker[/tt] to line up with the mouse pointer on the screen.

So here's the question: Does anyone have a method for converting the [tt]x[/tt] value from the [tt]MouseMove()[/tt] event to a corresponding position in the [tt]Plot Area[/tt] or [tt]Chart[/tt] that I can use to set the [tt]Left[/tt] property of [tt]Shape[/tt] at run time?

I have tried using the [tt]x[/tt] value directly, converting it with [tt]PointsToScreenPixelsX()[/tt], adjusting for [tt]Left[/tt] property of the both the [tt]Plot Area[/tt] and [tt]Chart[/tt], taken into account the [tt]Width[/tt] properties and everytime I have something close I change the Zoom on the chart and I'm back at square one.

Thanks in advance for any input you can provide,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

CMP,

Are you REALLY interested in the tracking line

or

do you want to be able to select a point on a series and react accordingly?

You might want to check out the GetChartElement method of the chart object. It uses the mouse coordinates (x,y) and returns the ElementID and two arguments that are context based.



Skip,

_[red][/red]

 

[CautionMP]

Skip,
Thanks for your response. Yes, I do want to track the line. If you want to see what I'm trying to acomplish check out the Java Chart at BigCharts.com. When you move the mouse over the Java Chart it displays a vertical line that tracks with the cursor.

I took a quick look at [tt]GetChartElement()[/tt] and it will help with phase 2 when (if?) I get there.

Thanks again,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Pretty cool! I'm impressed!

Your line seems to track the mouse pointer very well.

Are you working with the SHEET object or the WINDOW object?

Working with ZOOM applies to the WINDOW, so try pixels/points relative to the WINDOW.

Skip,

_[red][/red]

 

[CautionMP]

Skip,
Whatever object gets me where I want to go. I was playing around with [tt]GetChartElement()[/tt] to see if I could make it work and here is what I got:

Option Explicit

Private lngPlotAreaLeftX As Long, lngPlotAreaRightX As Long
Private varWindowZoom

Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
On Error Resume Next
Dim lngElementID As Long
Dim a As Long, b As Long
If varWindowZoom <> ActiveWindow.Zoom Then
  varWindowZoom = ActiveWindow.Zoom
  lngPlotAreaLeftX = 0
  lngPlotAreaRightX = 0
End If

ActiveChart.GetChartElement x, y, lngElementID, a, b

If lngElementID = xlPlotArea Then
  Me.Shapes("shpTracker").Visible = msoTrue
  If lngPlotAreaLeftX = 0 Or x < lngPlotAreaLeftX Then
    lngPlotAreaLeftX = x
  End If
    If lngPlotAreaRightX = 0 Or x > lngPlotAreaRightX Then
    lngPlotAreaRightX = x
  End If
  Me.Shapes("shpTracker").Left = Me.PlotArea.InsideLeft + Int(((x - lngPlotAreaLeftX) / (lngPlotAreaRightX - lngPlotAreaLeftX)) * Me.PlotArea.InsideWidth)
Else
  Me.Shapes("shpTracker").Visible = msoFalse
End If
End Sub

[tt]shpTracker[/tt] stays closer than anything else I've tried once I move the mouse over the entire [tt]Plot Area[/tt].

I'm still at a loss as to how to make this work.

Thanks again,
CMP

 

[xMRG]

This is probably what you are looking for:
"Converting from Mouse Coordinates to Data and Drawing Object Coordinates"

The link below has some exerpts from the book "Professional Excel Development" and includes some code to do the conversions you need.

http://www.informit.com/articles/article.asp?p=374498&seqNum=2&rl=1

BTW I bought the book and it is excelent. The focus of the book is on best practices for Excel development (both VBA and non VBA). The book has a good deal of informnation on how to employ classes, utilize non VBA methods for fast maintainable interactive workbooks, and use of Windows API calls.

Hope this helps.

xMRG

 

[CautionMP]

xMRG,
Thanks for your reply. I did run across this when I was first looking for a solution and it's by far one of the best examples I found.

It appears that this is really designed for imbedded charts and not chart sheets, as such here are some of the issues I ran across:
[ol][li]Works only when the zoom is 100% or greater.[/li]
[li]If the chart is not in the middle of the chart window it throws the position of the shape off by the amount of the offset.[/li]
[li]This routine cause the screen to flicker horribly when the mouse is moving.[/li][/ol]

Number three is not a deal breaker, I can optimize the routine to eliminate this once I have addressed issues one and two.

I continue to look for a solution and remain open to input from anyone.

Thanks again,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[sirfie]

Hi CautionMP,

I've interested with your project, have you completed it yet?
I'm too looking for a way for the mousemove event to work with my graph, If you've succedded in this project, please let me know will ya?.


thanks & regards