Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What key was pressed?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What key was pressed?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What key was pressed?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Was Delete Key pressed? | Excel Programming | |||
If pressed no to msgbox, then exit sub | Excel Programming | |||
Which button was pressed? | Excel Programming | |||
Enter pressed in Textbox | Excel Programming | |||
ComboBox DropDown when Key is pressed | Excel Programming |