Is This Possible In An Excel WorkSheet ?

Impossible and cannot be done. Excel does not support that functionality.

It sounds like you want to just glide or position your mouse over cell(s) and have the address of the nearby (below) cell be displayed in another cell, without selecting (clicking on) any cell(s). That is not possible to accomplish with naked cells. The closest you can come to that is a MouseMove event over a transparent command button, label, or some other ActiveX control set on top of a cell or range, whose properties, unlike spreadsheet cells, respond to MouseMove. That would have it's drawbacks too, among them is the increased difficulty in selecting the cell(s) with a mouse when a transparent control is placed over it like a plexiglass shield. Not to mention, this "shield" would need to cover all 16+ million worksheeet cells, making your request less practical to employ.

Thanks Tom for the quick reply.

I was actually thinking maybe one could use a combination of Mouse related APIs to accomplish this. ( GetCursorPos ; WindowFromPoint.....)

Anyway, it would be so nice to be able to see such a code

Regards.

Try these procedures, make sure you place them in separate modules. Especially, the second one might accomplish something close to what you are asking for. Special thanks to Hans Herber for these:


Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Private Type POINTAPI
x As Long
y As Long
End Type

Sub WhereAmI()
Dim pTargetPoint As POINTAPI
Dim lRetVal As Long
lRetVal = GetCursorPos(pTargetPoint)
MsgBox "My Position:" & vbLf & pTargetPoint.x & "," & pTargetPoint.y
End Sub


'*******************************************************


Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Public Type POINTAPI
x As Long
y As Long
End Type
Sub GetTheCursorPosition()
Dim lngCurPos As POINTAPI
Do
GetCursorPos lngCurPos
Cells(1, 1).Value = "X = " & lngCurPos.x
Cells(2, 1).Value = "Y = " & lngCurPos.y
DoEvents
Loop
End Sub

There is a round about way to do this!
Note the code coments

Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Public Type POINTAPI
x As Long
y As Long
End Type

Sub myReSet()
Range("A3").Select
End Sub

Sub GetTheMouseCursorPosition()
'Get Mouse position as x,y coordinates.
'Note: Once run you must hit "Ctrl-Break" to stop, with this test code or add a cell value to cell "A3!"

Dim lngCurPos As POINTAPI

Do
GetCursorPos lngCurPos
'Display the "X" position in cell "A1."
'Display the "Y" position in cell "A2."
Cells(1, 1).Value = "X = " & lngCurPos.x
Cells(2, 1).Value = "Y = " & lngCurPos.y
'Govern the reporting range?
If lngCurPos.x = 337 And lngCurPos.y = 419 Then GoTo myWin Else
'Back-Door sheet stop to end code!
If [A3] <> "" Then GoTo myStop Else
DoEvents
Loop
myWin:
Range("A3").Select
End

myStop:
End Sub

You can also try something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Report the address of the current selection to cell "A1" actively!
'Add code to Sheet Module!

Set mc = Target

[A1].Value = mc.Address(RowAbsolute:=False, ColumnAbsolute:=False)
End Sub

Thanks All.

I already tested a similar code before but with it, you cannot get the address of the Cell under the the Mouse pointer.You can only display the X and Y cursor absolute position.This is close but not quite what I want.

Also the DoEvents stops working when the Worksheet is in Edit Mode.

Regards.

Hi,

I am geting close to a solution.
I have this code which stores the the Tops & Lefts (In Pixels) of the range A1:F20 in 2 Arrays then I use a VlookUp function to return the Rows \ Columns that correspond to the current Cursor Coordinates which finally enables the Cell address to be displayed in cell A1.

The code must be assigned to a worksheet commandbutton.

When I run the code, if I move the mouse cursor over cell A1, the returned cell address is correct. However as I continue moving the mouse down, The returned cell address gradually becomes incorrect .

It is difficult to explain this problem. I am providing the code below so if anyone could test it and hopefully find out what I am doing wrong. ray:


Code :


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Base</SPAN> 1

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetCursorPos <SPAN style="color:#00007F">Lib</SPAN> "user32" (lpPoint <SPAN style="color:#00007F">As</SPAN> POINTAPI) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Type</SPAN> POINTAPI
x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>

<SPAN style="color:#00007F">Dim</SPAN> RwsTop()
<SPAN style="color:#00007F">Dim</SPAN> ClmnsLeft()
<SPAN style="color:#00007F">Dim</SPAN> lngCurPos <SPAN style="color:#00007F">As</SPAN> POINTAPI
<SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range


<SPAN style="color:#00007F">Sub</SPAN> Test()

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Rng = Range("A1:F20")
    <SPAN style="color:#00007F">ReDim</SPAN> RwsTop(1 <SPAN style="color:#00007F">To</SPAN> Rng.Rows.Count, 2)
    <SPAN style="color:#00007F">ReDim</SPAN> ClmnsLeft(1 To Rng.Columns.Count, 2)
    i = 1
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Rw <SPAN style="color:#00007F">In</SPAN> Rng.Rows
        RwsTop(i, 1) = ActiveWindow.PointsToScreenPixelsY(Rw.Top)
        RwsTop(i, 2) = Rw.Row
        i = i + 1
    <SPAN style="color:#00007F">Next</SPAN>
    
    i = 1
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Clmn <SPAN style="color:#00007F">In</SPAN> Rng.Columns
        ClmnsLeft(i, 1) = ActiveWindow.PointsToScreenPixelsX(Clmn.Left)
        ClmnsLeft(i, 2) = Clmn.Column
        i = i + 1
    <SPAN style="color:#00007F">Next</SPAN>
    
    <SPAN style="color:#00007F">Do</SPAN>
        GetCursorPos lngCurPos
        Column = Application.WorksheetFunction.VLookup(lngCurPos.x, ClmnsLeft, 2, <SPAN style="color:#00007F">True</SPAN>)
        Row = Application.WorksheetFunction.VLookup(lngCurPos.y, RwsTop, 2, <SPAN style="color:#00007F">True</SPAN>)
        Cells(1, 1) = Cells(Row, Column).Address
        DoEvents
    <SPAN style="color:#00007F">Loop</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Any suggestions will much appreciated.
Regards.

Any Suggestions ?

Thanks