What key was pressed?

[hanjohn]

Is it possible to detect which key the user pressed when entering a
cell value? Did they enter the value by pressing enter (return on Mac),
tab, any of the arrow keys or the Numpad enter key?
I want XL to check that the entered value is not greater than the value
in row 4 of the same column without using data validation.
The code I'm presently using is in the Workbook_SheetChange Event
Procedure but it only works properly when the user presses a key which
selects the cell directly below (or uses the mouse to directly select
that cell).
I want to change the code to cover instances where the user uses tab,
up arrow, left arrow, right arrow or enter on the Numpad to enter a
cell value. To do this I need to determine the key pressed by the user.
Any help will be much appreciated.
Ken Johnson

 

[Héctor Miguel]

hi, Ken !

... detect which key the user pressed when entering a cell value?...
... check... entered value is not greater than... row 4... same column without using data validation.
... I'm presently using... Workbook_SheetChange Event
... but it only works properly when the user... selects the cell directly below...
... want to... cover instances where the user uses tab, up arrow, left arrow, right arrow or enter...
... need to determine the key pressed by the user.

I'm not quite sure I'm following you properly, but...
don't forget IF the user selects directly another sheet-tab
you may want to give a try into same event with code like this [modify as needed]...
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row = 4 Then Exit Sub
If Application.IsText(Target) Then Exit Sub
If Application.IsText(Sh.Cells(4, Target.Column)) Then Exit Sub
If Target > Sh.Cells(4, Target.Column) Then
MsgBox "Value in " & Sh.Name & "!" & Target.Address & " *must NOT*" & vbCr & _
"be greater than " & Sh.Name & "!" & Sh.Cells(4, Target.Column).Address
Application.Undo
End If
End Sub

hth,
hector.

 

[hanjohn]

Thanks Hector!
After reading your reply I now realise I don't have to worry about
which key the user presses to enter the value. What I was after was a
way of figuring out the address of the last changed cell. All the time
it was staring me in the face, it's the target cell that changed!
Your reply has given me some other ideas too. I don't want the user to
type any text, just a number.
Thanks for going to the trouble of responding to and solving my silly
problem.
Ken Johnson