CODEWITHSUNDEEP
excelvba
Andrew Perry
Andrew Perry

Reputation: 765

How do I return the address of the currently selected cell and update it as the cursor moves?

I have a grid referencing the first two positions of an alphanumeric string, where each position can be A-Z or 0-9 (with a few letters excluded). The two axes are in column B and row 2 to permit more descriptive headers.

To aid in keeping track of where I am, I'd like the sheet to show in a separate cell the two-character reference for the currently selected cell.

VIN table showing current cell display example.

I've had a few ideas, but it's been three years since I did much VBA and my memory is failing me. Can anyone help me out?

Upvotes: 2

Views: 1676

Answers (2)

Tim Stack
Tim Stack

Reputation: 3248

Interesting idea!

Based on this answer on a similar post, you could check the cursor's location every x seconds. That way you won't have to manually execute the macro, and the cursor location will be updated automatically:

Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
' Create custom variable that holds two integers
Type POINTAPI
    Xcoord As Long
    Ycoord As Long
End Type

Sub GetCursorPosDemo()
    Dim llCoord As POINTAPI
    Dim rng As Range, destrng As Range

    ' Get the cursor positions
    GetCursorPos llCoord

    Set rng = GetRange(llCoord.Xcoord, llCoord.Ycoord)
    Set destrng = Thisworkbook.Sheets("Sheet1").Range("AK3") 'destination of cursor reference

    If Not rng Is Nothing Then
        destrng.Value = rng.Address
    Else
        destrng.Value = "N/A"
    End If

    Application.OnTime Now + TimeValue("00:00:01"), "Module1.GetCursorPosDemo" 'in case you place the sub in Module1
End Sub

Function GetRange(x As Long, y As Long) As Range
    Set GetRange = ActiveWindow.RangeFromPoint(x, y)
End Function

EDIT
As you request the cell reference to be in accordance with your self-made table:

Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
' Create custom variable that holds two integers
Type POINTAPI
    Xcoord As Long
    Ycoord As Long
End Type

Sub GetCursorPosDemo()
    Dim llCoord As POINTAPI
    Dim destrng As Range

    ' Get the cursor positions
    GetCursorPos llCoord

    Set destrng = Thisworkbook.Sheets("Sheet1").Range("AK3") 'destination of cursor reference

    destrng.Value = GetRange(llCoord.Xcoord, llCoord.Ycoord)

    Application.OnTime Now + TimeValue("00:00:01"), "Module1.GetCursorPosDemo" 'in case you place the sub in Module1
End Sub

Function GetRange(x As Long, y As Long) As String
    Dim ActualRange As Range
    Set ActualRange = ActiveWindow.RangeFromPoint(x, y)

    If ActualRange.Column > 2 And ActualRange.Row > 2 Then
        GetRange = ActiveWindow.Cells(ActualRange.Row, "B") & ActiveWindow.Cells(2, ActualRange.Column)
    Else
        GetRange = "N/A"
    End If    
End Function

As this method uses Application.OnTime, and thus no Do...Loop, other methods can run at the same time as this code. The code above gets put at a halt while the other method runs. With a loop, no other code can run.

Upvotes: 2

EvR
EvR

Reputation: 3498

try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("AK3").Value = Cells(Target.Row, 2) & Cells(2, Target.Column)
End Sub

and get rid of the merged cells ;-)

Upvotes: 2

Related Questions