mouse position in EXCEL

[Franz]

Hi guys

Is there anybody who could tell me the sequence to read the mouse
position into a macro?

I'd like to determine the position of the mouse on a chart when the
mouse button was pressed to take some actions.

Example: while hovering over a chart that presents a map I'd like to
have the possibility to zoom in and center on a specific point.

FB

 

[ben]

If you use the Chart_mousedown event, this will return
1. the button pressed down
2. whether or not Shift or ctrl is being pressed
3. the position of the mouse over the chart

 

[Franz]

hi Ben

Tks for your help.

Could you please explain a bit more in detail? I tried to use that
event but I couldn't make it work. Do I have to assign a macro to the
chart? How would the macro look like to capture the mouse buttons and
position?

 

[Jake Marx]

Hi Franz,

Could you please explain a bit more in detail? I tried to use that
event but I couldn't make it work. Do I have to assign a macro to the
chart? How would the macro look like to capture the mouse buttons and
position?

If this chart is in a chartobject (ie, on a worksheet), I believe you would
have to create an event sink for the chart using a class module. Here are
the steps to do that:

1) Insert a new class module and name it "CChartSink"

2) Add the following code to that class module:

Public WithEvents cht As Excel.Chart

Private Sub cht_MouseUp(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
MsgBox "Button: " & Button
MsgBox "Shift: " & Shift
MsgBox "X: " & x & ", Y: " & y
End Sub

3) Put the following code in a standard module:

Private mobjChartSink As CChartSink

Private Sub mStartChartSink(rcht As Excel.Chart)
Set mobjChartSink = New CChartSink

Set mobjChartSink.cht = rcht
End Sub

Private Sub mStopChartSink()
On Error Resume Next
Set mobjChartSink.cht = Nothing
Set mobjChartSink = Nothing
End Sub

Public Sub gDemoStart()
mStartChartSink Worksheets("Sheet1").ChartObjects(1).Chart
End Sub

Public Sub gDemoStop()
mStopChartSink
End Sub


4) Change the references in the gDemoStart routine as needed (worksheet
name, chartobject name or index).

5) Close the VBE, then run the macro gDemoStart.

6) When you're done testing, run gDemoStop.


When you're done testing, you could call the gDemoStart routine from the
Workbook_Open event and the gDemoStop routine from the Workbook_BeforeClose
event.

A few things I noticed:

1) The clicking is a little strange - the user may have to click twice on
the chart in order to trigger the event (if the first click is not on the
chart itself or one of the chart labels).

2) To remedy #1, I tried setting the locked property of the chartobject and
protecting the worksheet. However, this disables clicking on the chart and
thus the events don't fire.


Another alternative you could try is the Windows API function GetCursorPos.
Paste the following code into a standard code module:

Public Type POINTAPI
x As Long
y As Long
End Type

Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As
Long

Sub DemoWithAPI()
Dim ptAPI As POINTAPI
Dim lChartX As Long
Dim lChartY As Long

GetCursorPos ptAPI

With Worksheets("Sheet1").ChartObjects(1)
lChartX = ptAPI.x - .Left
lChartY = ptAPI.y - .Top
End With

MsgBox "X:" & lChartX & ", Y:" & lChartY
End Sub


Then assign the macro DemoWithAPI to the chartobject. Now, when the user
clicks the chart, the actual *Screen* position in pixels will be displayed.
I'm not entirely sure how to convert those coordinates to the equivalent
chart coordinates, but maybe you can play with it a bit.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Franz]

Ben

I have no idea how to make this work.

According to the EXCEL help I did the following:

- I inserted a new CLASS MODULE and named it "EventClassModule" and
inserted the declaration "Public WithEvents myChartClass As Chart" as
the 1st line
- Then I inserted the sequence below in a new module:

Dim myClassModule As New EventClassModule
-------------------------------------------------------------------------------------
Sub InitializeChart()
Set myClassModule.myChartClass =
Worksheets(1).ChartObjects(1).Chart
End Sub

Excel help says: "After you run the InitializeChart procedure, the
"myChartClass" object in the class module points to embedded chart one
on worksheet one, and the event procedures in the class module will run
when the events occur."

Then I included the sequence below in the CLASS MODULE:

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
MsgBox "Button = " & Button & chr$(13) & "Shift = " & Shift &
chr$(13) & "X = " & X & " Y = " & Y
End Sub

However nothing seems to work. I get the following error when running
the initialization macro: "Unable to get the ChartObjects property of
the Worksheet class"

Then I added this macro to the class module:

Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
MsgBox "Button = " & Button & Chr$(13) & "Shift = " & Shift &
Chr$(13) & "X = " & X & " Y = " & Y
End Sub

But nothing happens when I click the mouse botton. After I assigned the
Sub "Chart_MouseDown" to the chart I get this error when pressing a
button: "Argument not optional"

What in heaven's name I need to do to make this thing work?

 

[Franz]

Jake you're my hero..... it worked perfectly .... thanks

Hey, do you know how to read the COM port into Excel?

I have GPS that I would like to read out in real time. The idea behind
this is to try to process the coordinates to show them moving on a
map.....

If this works, I could run this on a PalmTop or on a tablet PC. The
next step would then be to process the movement by comparing the GPS
strings i.e. to simulate a variometer (device used by sailplanes)
etc....

 

[Jake Marx]

Hi Franz,

Jake you're my hero..... it worked perfectly .... thanks

Glad to help.

Hey, do you know how to read the COM port into Excel?

I've never done it before, but there is an MSComm ActiveX control that will
do this type of thing. I think it ships with VB 6 and maybe with Office
Developer edition. Here's more info on it:

http://msdn.microsoft.com/library/en-us/comm98/html/vbobjcomm.asp

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]