Cell Address of Mouse Pointer

Originally Posted by FDibbins

I know you can do that in powerpoint, but i have nevr seen that in any version of excel

I checked out this functionality in PowerPoint and it looks to be exactly what she described. I'm wondering if she has her applications mixed up because I certainly have never used, or seen, a feature like this in Excel.

p.s. I tried the VBA route and I couldn't get the code to work, plus she want's this functionality to work universally across all spreadsheets.

cells in PowerPoint?

Intresting

Its API code credit to the author -- have fun

In a standard module:

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

Type POINTAPI
x As Long
Y As Long
End Type

Dim lngCurPos As POINTAPI
Dim TimerOn As Boolean
Dim TimerId As Long
Public oldColor As Long
Dim newRange As Range
Dim oldRange As Range

Sub StartTimer()
If Not TimerOn Then
TimerId = SetTimer(0, 0, 0.01, AddressOf TimerProc)
TimerOn = True
Else
MsgBox "Timer already On !", vbInformation
End If
End Sub

Sub TimerProc()
On Error Resume Next
GetCursorPos lngCurPos
Set newRange = ActiveWindow.RangeFromPoint(lngCurPos.x, lngCurPos.Y)
If newRange.Address <> oldRange.Address Then Range("A1").Value = newRange.Address
Set oldRange = newRange
End Sub

Sub StopTimer()
If TimerOn Then
KillTimer 0, TimerId
TimerOn = False
Else
MsgBox "Timer already Off", vbInformation
End If
End Sub


In the worksheet module:

Option Explicit

Dim TrgtColor As Long
Dim oldTarget As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = TrgtColor
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set oldTarget = Target
TrgtColor = oldColor
End Sub

To activate the code, run the "StartTimer" macro and to stop it, run the "StopTimer" macro.

Maybe replace this bit code as makes easier to read in cell A1

Sub TimerProc()
On Error Resume Next
GetCursorPos lngCurPos
Set newRange = ActiveWindow.RangeFromPoint(lngCurPos.x, lngCurPos.Y)
If newRange.Address <> oldRange.Address Then Range("A1").Value = newRange.Address(False, False)
'\\ jiuk - note used false / false to take away the default anchors 
Set oldRange = newRange
End Sub

Or you could change destination cell A1 to status bar read out and cancel on exit ie

Use this code to take control of the status bar

Application.StatusBar =

And add this code to cancel on exit or stop which will give control of the statusbar back to the programme

 Application.StatusBar = False

Code work perfectly and shows the mouse CELLs lcation in cell A1....

This is a good start, why does the code not work for you, follow my instuctions as to where to put the codes

ie some in the standard module, some in the worksheet module

Ermm re RE tested and yes the code works on all worksheets in the workbook and reports to cell A1 each times

Not sure what You are doing incorectly

Jack,

Code looks good. For whatever reason the font color in A1 matched the background and therefore was not apparent. I think this is good if A1 is always viewable. I may be able to work with royUK's code. Thanks for all the contributions. I'll mark this solved.

Yes agreed so see the post above re status bar ... that would work even better