Offsets based on key stroke or prior cursor posistion

[lythander]

All:

I am still attempting to get a set of 5 cells to work like radial buttons. The system I currently have *works* but only if you exit a cell by pressing enter or the down arrow. In an attempt to make the code more thorough, I thought it would be nice if they can push any button.

I believe there are two ways to do this. I would prefer the first solution, but I don't even know where to begin coding it.

Solution One: The Ideal Solution

First would be some bit of code that can tell me where I *was* focused. For example, if I was in A1, and preseed enter, or the right arrow key, the system would know it came from A1. This would be the ideal solution, but I don't believe it's possible in a Worksheet_Change situation. The reason it's ideal is it would work even with mouse clicks.

The code would start off with that location stored as a cell row and column, so that I could return to it and offset accordingly.

Solution Two: My Current Attempt

The other option I have, is to determine if the up arrow or right arrow was used. But, the code I currently have is giving me a variable not defined for this line (Private Sub Worksheet_Change(ByVal Target As Range)) But this solution wouldn't work for mouse clicks, it *is* better though.

As always, I thank you for your insight. I'm still a novice in the coding world, but I get better everytime I visit here!

Here's the first part of the code

Option Explicit

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Private Const VK_UP = &H26
Private Const VK_RIGHT = &H27
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False

If GetKeyState(VK_RIGHT) = True Then
     MsgBox "You pressed the right arrow." 'this just here for validation
     Direction = 1 'direction is right
Else
If GetKeyState(VK_UP) = True Then
     MsgBox "You did not press the up arrow" 'this just here for validation
     Direction = 2 'direction is Up
Else
Direction = 3 'direction is down or they pressed enter
End If
End If
End If

If Target.Column = 3 Then

'Determine what you need to do based on direction pressed
If Direction = 3 Then 'down arrow or enter
    ActiveCell.Offset(-1, 1).Select
    ActiveCell.FormulaR1C1 = ""
Else
If Direction = 2 Then 'up
    ActiveCell.Offset(1, 1).Select
    ActiveCell.FormulaR1C1 = ""
Else
If Direction = 1 Then 'right
    ActiveCell.FormulaR1C1 = ""
End If
End If
End If

        For x = 1 To 3
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = ""
        Next x
    
    ActiveCell.Offset(1, -4).Select

'Repeat for columns 4, 5 and 6 with minor posistion changes, all of this 'code works
'Else
'If Target.Column = 4 Then
'    ActiveCell.Offset(-1, -1).Select

 

[BrianB]

Try this :-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static LastCell As String
    MsgBox ("Current Cell = " & Target.Address & vbCr & "Last Cell was " & LastCell)
    LastCell = Target.Address
End Sub