Excel Question: Can I detect last key pressed? 2

[ssmgr]

Is there any way to detect the last key pressed in a worksheet? I would like to put some code inside a Worksheet.SelectionChange sub to ascertain whether the user pressed Enter, Tab, Left/Right/Up/Down arrows, or clicked on the newly selected cell with a mouse. I see that there are mechanisms to detect KeyPress etc on a form, but I want to detect keystrokes on an Excel worksheet.

Any advice appreciated.

Steve

 

[xlbo]

not specifically but you can use the SELECTION change event to log the cell addresses as they change - you would then need logic to determine how it changed. Summat like this:

Public cCell As Range, pCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cRow As Long, pRow As Long
Dim cCol As Integer, pCol As Integer
Dim colMove As Integer, rowMove As Long

If pCell Is Nothing Then Set pCell = Target
Set cCell = Target

cRow = cCell.Row
cCol = cCell.Column
pRow = pCell.Row
pCol = pCell.Column

colMove = cCol - pCol
rowMove = cRow - pRow

MsgBox "Col Movement = " & colMove & vbCrLf & "Row movement = " & rowMove

Set pCell = cCell
End Sub

You would need to use a SELECT CASE statement on colMove and rowMove to determine what movement occured eg
rowMove
1 = 1 cell down
-1 = 1 cell up

colMove
1 = 1 cell to the right
-1 = 1 cell up

anything other than 1 or -1 is mouse selection

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ssmgr]

Thanks for that Geoff, I'd never considered doing something as simple as declaring global range variables and setting them to the selected cells. A well-deserved star has gone your way.

Steve