number pad key id?

**mperrah** · 12-04-2007, 11:55 AM

I'm trying to use the onkey event
for the numbers of the ten key (number pad)
just capturing "4" works in the numbers above the letters.
How do I capture the numberpad "4"?

Thanks in advance.
Mark

**mperrah** · 12-04-2007, 01:18 PM

is there a way to list the chr() value for number pad 4, 5, 6, and 7

**mikerickson** · 12-04-2007, 05:02 PM

I put this code in a userform and got that both the normal and keypad "3" keys return a keyAscii of 51, however the normal "3" returns Keycode = 51 and the keypad "3", KeyCode=99.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    MsgBox "KeyCode " & KeyCode
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    MsgBox "keyAscii " & KeyAscii
End Sub

**mperrah** · 12-04-2007, 06:38 PM

I did find the values of the numpad keys 4,5,6 and 7 are 101, 102, 103 and 104
I tried using the chr(101) but it errors out.
This is the code I'm trying to modify to use the number pad keys:

Option Explicit
Sub onkeyOn()
        Application.OnKey "1", "action_p"  ' tried Application.OnKey chr(101) , "action_p"
        Application.OnKey "2", "action_f" ' how to use (KeyCode = 102) in place of "2"
        Application.OnKey "3", "action_n"
        Application.OnKey "4", "action_"
End Sub
Sub onkeyOff()
        Application.OnKey "1"
        Application.OnKey "2"
        Application.OnKey "3"
        Application.OnKey "4"
End Sub
Sub action_p()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("p")
End Sub
Sub action_f()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("f")
End Sub
Sub action_n()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("n")
End Sub
Sub action_()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("")
End Sub
Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
'     if not inside target - on target sheet
        If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "1"
            ElseIf vletter = "f" Then
            vletter = "2"
            ElseIf vletter = "n" Then
            vletter = "3"
            ElseIf vletter = "" Then
            vletter = "4"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
        Else
        ' if not outside target
            ActiveCell.Value = vletter
            ActiveCell.Offset(, 1).Select
        End If
        ElseIf ActiveSheet.Name <> "Data" Then
        ' if not active sheet data
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "1"
            ElseIf vletter = "f" Then
            vletter = "2"
            ElseIf vletter = "n" Then
            vletter = "3"
            ElseIf vletter = "" Then
            vletter = "4"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
     End If
End Sub

**rory** · 12-05-2007, 03:53 AM

The number keys 0-9 are represented by 96-105. You can use:

Application.Onkey "{100}", "proc_name"

to hook the 4 on the number pad.

**mperrah** · 12-05-2007, 12:23 PM

So like this

Option Explicit

Sub onkeyOn()
    With Application
    .Calculate
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .OnKey "{100}", "action_p"
    .OnKey "{101}", "action_f"
    .OnKey "{102}", "action_n"
    .OnKey "{103}", "action_"
         End With
End Sub

Sub onkeyOff()
    With Application
    .Calculate
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .OnKey "{100}"
    .OnKey "{101}"
    .OnKey "{102}"
    .OnKey "{103}"
    End With
End Sub

Sub action_p()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("p")
End Sub

Sub action_f()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("f")
End Sub

Sub action_n()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("n")
End Sub

Sub action_()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("")
End Sub

Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
    '     if not inside target - on target sheet
    If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
        SendKeys "{F2}"
        If vletter = "p" Then
            vletter = "{100}"
            ElseIf vletter = "f" Then
            vletter = "{101}"
            ElseIf vletter = "n" Then
            vletter = "{102}"
            ElseIf vletter = "" Then
            vletter = "{103}"
        End If
        ActiveCell.Value = vletter
        SendKeys "{F2}"
        Else
        ' if not outside target
        ActiveCell.Value = vletter
        ActiveCell.Offset(, 1).Select
    End If
   ElseIf ActiveSheet.Name <> "Data" Then
    ' if not active sheet data
    SendKeys "{F2}"
    If vletter = "p" Then
        vletter = "{100}"
        ElseIf vletter = "f" Then
        vletter = "{101}"
        ElseIf vletter = "n" Then
        vletter = "{102}"
        ElseIf vletter = "" Then
        vletter = "{103}"
    End If
    ActiveCell.Value = vletter
    SendKeys "{F2}"
     End If
End Sub

**mperrah** · 12-05-2007, 12:25 PM

Thank you. I thought it was do-able. You rock again...

How can I make both number pad and top row numbers fire the event?

**Norie** · 12-05-2007, 12:40 PM

Couldn't you actually use an event? eg Worksheet_Change?

**mperrah** · 12-05-2007, 12:44 PM

This catches both number sources,
but out of the range puts {101} instead of 4

Sub onkeyOn()
    With Application
.Calculate
.ScreenUpdating = False
.Calculation = xlCalculationManual
        .OnKey "{100}", "action_p"
        .OnKey "4", "action_p"
        .OnKey "{101}", "action_f"
        .OnKey "5", "action_f"
        .OnKey "{102}", "action_n"
        .OnKey "6", "action_n"
        .OnKey "{103}", "action_"
        .OnKey "7", "action_"
         End With
End Sub
Sub onkeyOff()
    With Application
        .Calculate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .OnKey "{100}"
        .OnKey "4"
        .OnKey "{101}"
        .OnKey "5"
        .OnKey "{102}"
        .OnKey "6"
        .OnKey "{103}"
        .OnKey "7"
    End With
End Sub
Sub action_p()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("p")
End Sub
Sub action_f()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("f")
End Sub
Sub action_n()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("n")
End Sub
Sub action_()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("")
End Sub
Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
'     if not inside target - on target sheet
        If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "{100}"
            ElseIf vletter = "f" Then
            vletter = "{101}"
            ElseIf vletter = "n" Then
            vletter = "{102}"
            ElseIf vletter = "" Then
            vletter = "{103}"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
        Else
        ' if not outside target
            ActiveCell.Value = vletter
            ActiveCell.Offset(, 1).Select
        End If
        ElseIf ActiveSheet.Name <> "Data" Then
        ' if not active sheet data
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "{100}" ' how to change this so letter or number stay unchanged?
            ElseIf vletter = "f" Then
            vletter = "{101}"
            ElseIf vletter = "n" Then
            vletter = "{102}"
            ElseIf vletter = "" Then
            vletter = "{103}"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
     End If
End Sub

**mperrah** · 12-05-2007, 01:03 PM

maybe add an if statement to the action_... sub

Sub action_p() 
    If Selection.Count > 1 Then Exit Sub 
      if selection.value = "{100}"
    Call insert_letter("pnum") 
      elseif selection.value = "4"
    Call insert_letter("ptop") 
End Sub

Then change the vletter call to match?

**mperrah** · 12-05-2007, 01:12 PM

Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
'     if not inside target - on target sheet
        If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
            SendKeys "{F2}"
            If vletter = "pnum" Then
            vletter = "{100}"
            ElseIf vletter = "ptop" Then
            vletter = "4"
'....
      Else
        ' if inside target
            ActiveCell.Value = vletter
            ActiveCell.Offset(, 1).Select
        End If
        ElseIf ActiveSheet.Name <> "Data" Then
        ' if not active sheet data
            SendKeys "{F2}"
            If vletter = "pnum" Then
            vletter = "{100}"
            ElseIf vletter = "ptop" Then
            vletter = "4"

this blocks both top 4 and numpad 4....
typing either enters nothing

**mperrah** · 12-05-2007, 01:33 PM

Ok,
This works using top row or numberpad,
but, if I type using numberpad keys (Laptop Num Lock)
out of the target area then in the target area, I get the key value
my numlock key "4" is a "u" with numlock off
When I type u in target I get "p" (which is the desired result)
when I type u out of target I get "u" (Also good)
but after typing out of the target and then type in the target I get "u"
instead of the "p" I am expecting ???

If I turn off numlock and then back on, the "u" returns a "p" again as expected... strange

Option Explicit
Sub onkeyOn()
    With Application
        .Calculate
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .OnKey "{100}", "action_p"
        .OnKey "4", "action_p"
        .OnKey "{101}", "action_f"
        .OnKey "5", "action_f"
        .OnKey "{102}", "action_n"
        .OnKey "6", "action_n"
        .OnKey "{103}", "action_"
        .OnKey "7", "action_"
         End With
End Sub
Sub onkeyOff()
    With Application
        .Calculate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .OnKey "{100}"
        .OnKey "4"
        .OnKey "{101}"
        .OnKey "5"
        .OnKey "{102}"
        .OnKey "6"
        .OnKey "{103}"
        .OnKey "7"
    End With
End Sub
 
Sub action_p()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("p")
End Sub

Sub action_f()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("f")
End Sub

Sub action_n()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("n")
End Sub

Sub action_()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("")
End Sub

Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
'     if not inside target - on target sheet
        If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "4"
            ElseIf vletter = "f" Then
            vletter = "5"
            ElseIf vletter = "n" Then
            vletter = "6"
            ElseIf vletter = "" Then
            vletter = "7"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
        Else
        ' if inside target
            ActiveCell.Value = vletter
            ActiveCell.Offset(, 1).Select
        End If
        ElseIf ActiveSheet.Name <> "Data" Then
        ' if not active sheet data
            SendKeys "{F2}"
            If vletter = "p" Then
            vletter = "{100}"
            ElseIf vletter = "f" Then
            vletter = "{101}"
            ElseIf vletter = "n" Then
            vletter = "{102}"
            ElseIf vletter = "" Then
            vletter = "{103}"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
     End If
End Sub

**rory** · 12-05-2007, 03:50 PM

There used to be a bug with SendKeys and NumLock, but as I recall that only affected Access (I may be wrong, that may just be the context I was in when reading about it). Presumably if you are getting "u" rather than 4 outside the target area, the NumLock must be off?

**rory** · 12-05-2007, 03:52 PM

You would probably be better off using the SelectionChange event to turn the OnKey on and off, rather than trying to handle it in the called sub.

**mperrah** · 12-05-2007, 07:23 PM

I have a custom menu that calls the sub to fire the onkey
The code for the onkey is just as I posted in a normal module.
With the numlock On, the code works fine,
typing "u" in the target gets a "p" as desired, all is fine...
until I type outside the target.
Outside the target gets the result desired (typing "u" [=numpad4] places "u"
But back in the target, typing "u" should be "p", but it gets "u"

After the glitch, I turn off numlock, and then turn numlock back on,
and the sub works fine again.... until I type outside the target area.

The problem is repeatable and fixable, wonder if there is a onfocus change that can turn numlock on and off, depending on the target??
Or if this is a known glitch, I can prompt the user of the awkward fix.

Thank you for your persistence.
Mark

**mperrah** · 12-05-2007, 07:28 PM

This works on my normal desk top keybpoard with Excel 2003
My laptop has Excel 2007 and it has the glitch.

Option Explicit
Sub onkeyOn()
    With Application
        .Calculate
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .OnKey "{100}", "action_p"
        .OnKey "4", "action_p"
        .OnKey "{101}", "action_f"
        .OnKey "5", "action_f"
        .OnKey "{102}", "action_n"
        .OnKey "6", "action_n"
        .OnKey "{103}", "action_"
        .OnKey "7", "action_"
    End With
End Sub

Sub onkeyOff()
    With Application
        .Calculate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .OnKey "{100}"
        .OnKey "4"
        .OnKey "{101}"
        .OnKey "5"
        .OnKey "{102}"
        .OnKey "6"
        .OnKey "{103}"
        .OnKey "7"
    End With
End Sub
 
Sub action_p()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("p")
End Sub

Sub action_f()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("f")
End Sub

Sub action_n()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("n")
End Sub

Sub action_()
    If Selection.Count > 1 Then Exit Sub
    Call insert_letter("")
End Sub

Sub insert_letter(vletter As String)
    If ActiveSheet.Name = "Data" Then
         '     if not inside target - on target sheet
        If ActiveCell.Column < 18 _
        Or ActiveCell.Column > 44 _
        Or ActiveCell.Row > 1000 Then
            SendKeys "{F2}"
            If vletter = "p" Then
                vletter = "4"
            ElseIf vletter = "f" Then
                vletter = "5"
            ElseIf vletter = "n" Then
                vletter = "6"
            ElseIf vletter = "" Then
                vletter = "7"
            End If
            ActiveCell.Value = vletter
            SendKeys "{F2}"
        Else
             ' if inside target
            ActiveCell.Value = vletter
            ActiveCell.Offset(, 1).Select
        End If
    ElseIf ActiveSheet.Name <> "Data" Then
         ' if not active sheet data
        SendKeys "{F2}"
        If vletter = "p" Then
            vletter = "4"
        ElseIf vletter = "f" Then
            vletter = "5"
        ElseIf vletter = "n" Then
            vletter = "6"
        ElseIf vletter = "" Then
            vletter = "7"
        End If
        ActiveCell.Value = vletter
        SendKeys "{F2}"
    End If
End Sub

**mperrah** · 12-05-2007, 07:45 PM

I have these on the worksheet code to fix formulae that get tweaked if rows are added or removed (was happening often)

Not sure how to call the onkey for the selection change.
I thought just changing the focus wouldn't work because the value is not entered yet.
But if the focus turns the onkey active, would the current selection trigger the event, as I type?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 1 Then
        With Target
            If .Row < 3 Then
ElseIf .Row = 3 Then
            .Offset(, 1).Formula = "=IF(C" & .Row & "=0,"""",1)"
            .Offset(, 46).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            .Offset(, 47).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 48).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 49).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            ElseIf .Row > 3 Then
            .Offset(, 1).Formula = "=IF(C" & .Row & "=0,"""",B" & .Row - 1 & "+1)"
            .Offset(, 46).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            .Offset(, 47).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 48).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 49).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            End If
        End With
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CheckmarkCells As Range
    Dim passcomplete As Range
    Dim failcomplete As Range
Set passcomplete = Range("P3:P1000")
    Set failcomplete = Range("Q3:Q1000")
    Set CheckmarkCells = Range("A3:A1000")
If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, CheckmarkCells) Is Nothing Then
            With Target
                If .Font.Name = "Arial" Then
                    .Font.Name = "Marlett"
                    .Value = "a"
                    .Offset(0, 1).Select
                Else
                    If .Font.Name = "Marlett" And .Value = "a" Then
                        .ClearContents
                        .Font.Name = "Arial"
                        .Offset(0, 1).Select
                    End If
                End If
            End With
        End If
If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, passcomplete) Is Nothing Then
            With Target
                If .Value = "" Then
                .Value = "Pass"
                .Offset(0, 1).ClearContents
                Else
                    If .Value = "Pass" Then
                        .ClearContents
                    End If
                End If
            End With
        End If
If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, failcomplete) Is Nothing Then
            With Target
                If .Value = "" Then
                .Value = "Fail"
                .Offset(0, -1).ClearContents
                Else
                    If .Value = "Fail" Then
                        .ClearContents
                    End If
                End If
            End With
        End If
If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column = 2 Then
        With Target
            If .Row < 3 Then
ElseIf .Row = 3 Then
            .Offset(, 0).Formula = "=IF(C" & .Row & "=0,"""",1)"
            .Offset(, 45).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            .Offset(, 46).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 47).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 48).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            ElseIf .Row > 3 Then
            .Offset(, 0).Formula = "=IF(C" & .Row & "=0,"""",B" & .Row - 1 & "+1)"
            .Offset(, 45).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            .Offset(, 46).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 47).Formula = "=IF(AT" & .Row & "="""","""",inspector)"
            .Offset(, 48).Formula = "=IF(AT" & .Row & "="""","""",inspected)"
            End If
        End With
    End If
End Sub

**mperrah** · 12-05-2007, 07:53 PM

This works but seems to lag with the calculations turning to manual and back to automatic with each change

Maybe put the calc change in the focus change event rather than the onkey sub?
What do you think?
On the worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
        If Target.Column < 18 _
        Or Target.Column > 44 _
        Or Target.Row < 3 _
        Or Target.Row > 1000 Then
Call onkeyOff
        Else
        Call onkeyOn
        End If
End Sub

**rory** · 12-06-2007, 03:47 AM

Hard to say without knowing why you're doing all this - why do you turn calc off and on?
Incidentally, do you have ranges named 'inspector' and 'inspected'?

**mperrah** · 12-06-2007, 10:32 AM

Yes to the named ranges, they have a cell formula that looks at a cell value based on row number.
If the user inserts or deletes a row the formula stopped working.
So (with this forums help) we found a way to replace the formula if anything changes.

As far as the calculations, the onkey event associated with selection change seemed to make the response time lag.
So I made a menu item linked to the onkey sub and the user can turn it on or off at will.

Also, I have a few subs that scan an entire sheet, process data and copy and paste between sheet.
(I think) the onkey event linked to the selection changed made these sub come to a stand still.
I found that turning calc to manual speed everything up.
At first I turned calc to manual on workbook open,
but some formulae need to calc on the fly,
So I added the calc change to the onkey sub,
so when they fire the onkey, calc goes to manual,
when they turn onkey off calc goes back to automatic.
Also all the subs that do copy and paste on the sheet using the onkey,
I start the sub with calc changed to manual,
then end the sub with calc back to automatic.

What I'm trying to do is within a target area Sheet("Data").Range("R3:AR" & lastrow)
I type in p, f, n and (space)
since these keys are somewhat far apart, and my typing skills are less then fast, I reset the keys typed to numpad 4, 5, 6, and 7
I only need the keys replaced within the target range and only on this sheet. The ability to turn the onkey on or off is not really necessary,
but since it seemed to slow down other processes and other sheets posed error handling problems I added the on/off option.

If there is a way to target the area used better, or make calc not take place just when typing in the target, I think we would have a winner.

I started running into trouble when I tried coding for the numlock keys on my laptop. Desktop numkeys seem to work fine.
To make a work around I was trying to code for both regular num keys and numpad num keys. So far it seems to work until I type outside the target with the onkey active, typing back in the target glitches (laptop only)

Thread: number pad key id?

Thread Tools

Display

number pad key id?

Posting Permissions