Location of Cursor in Excel 1

[PBAPaul]

I have a spreadsheet with 8 columns.
There are 8 rows starting at row 2.

What I want to do is to show in row 1 a calculation - which is dependent on the column in which the cursor is in and in previous calculations - in the column in which the cursor is located. The problem is that I do not want the user to have to click in the column until he/she has seen the calculation.

Is there any way in VBA to get the location of the cursor as it moves around the screen?

Any help would be greatly appreciated.

Paul

 

[PHV]

Have a look at the ActiveCell.Row and ActiveCell.Column properties.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PBAPaul]

Thanks PH but my question was not that simple!

If I click on C3 then I can obviously use the Activecell properties to get the column and row designation.

What I want to do is get the column designation of where the cursor is. Let's say I click on C3 then move the cursor away to be in A2 BUT WITHOUT ANY OTHER ACTION ie I move the cursor on screen but do not select any cell.

If you insert comments into various cells then placing the cursor over the cells you can see the comments. How does Excel know the location of the cursor on the screen so that it can bring up the appropriate comment?

Basically I want to get the XY co-ordinates of the cursor at any time and so by doing, check which column the cursor is in and if it is in the range of rows in which I have data.

Paul

 

[PHV]

So, you talked about the mouse pointer.

 

[combo]

With the help of Ivan Moala's code for windows timer (please note that callback is used, so save the project before you run the code, don't start in VBE, start with 'GoTimer' terminate with 'DeactivateMyTimer'), standard module code:

Option Explicit
Declare Function SetTimer _
    Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerfunc As Long) _
As Long

Declare Function KillTimer _
    Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal nIDEvent As Long) _
As Long

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

Public Type POINTAPI
    X As Long
    Y As Long
End Type

'==========Public Declarations ==============================
Public TimerID As Long            'Turn on and off with this ID
Public TimerActive As Boolean     'Is the timer active
Public Point As POINTAPI          'Cursor data structure
'============================================================


Public Sub GoTimer()
Call ActivateMyTimer(0.25)
End Sub

Public Sub ActivateMyTimer(ByVal sec As Long)
sec = sec * 1000
If TimerActive Then Call DeActivateMyTimer

On Error Resume Next
TimerID = SetTimer(0, 0, sec, AddressOf Timer_CallBackFunction)
TimerActive = True

End Sub

Public Sub DeActivateMyTimer()
    KillTimer 0, TimerID
End Sub

Public Sub Timer_CallBackFunction(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
Dim objRange As Object
GetCursorPos Point
Set objRange = ActiveWindow.RangeFromPoint(X:=Point.X, Y:=Point.Y)
If Not objRange Is Nothing Then
    If TypeName(objRange) = "Range" Then
        Application.StatusBar = objRange.Address
    End If
End If

If Not TimerActive Then Call DeActivateMyTimer

End Sub

combo

 

[combo]

In my above code some error handling is necessary, to avoid kicking off in some cases. For instance:

Public Sub Timer_CallBackFunction(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, _
    ByVal Systime As Long)
Dim objRange As Object
On Error GoTo errh
GetCursorPos Point
Set objRange = ActiveWindow.RangeFromPoint(X:=Point.X, Y:=Point.Y)
If Not objRange Is Nothing Then
    If TypeName(objRange) = "Range" Then
        Application.StatusBar = objRange.Address
    Else
        Application.StatusBar = False
    End If
Else
    Application.StatusBar = False
End If

errh:
If Not TimerActive Then Call DeActivateMyTimer

End Sub

combo

 

[PBAPaul]

Thanks Combo

Have a star!

Paul