number pad key id?

[mperrah]

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]

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

[mikerickson]

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]

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]

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]

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]

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]

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

[mperrah]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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)

[Norie]

Couldn't you use data validation?

With that you could create a dropdown list with each of the option in each of the cells.

And if you are on Excel 2000 or later a worksheet change event will be triggered when you select from the dropdowm.

[mperrah]

that sounds like too much effort on the users part.
My goal was to speed up a repetative task of entering 3 letters.
And eliminate the need to hit enter or arrows after each entry.

I suppose clicking might be a plus.

So they click the cell, pick the letter then focus goes to the next cell to the right?

Is there a way to make the drop down list be active by default.
The data validations lists I have made need to be clicked first to activate,
then a second click to get the drop down,
then a third click to choose an entry...
most users can type a "p" faster than that even if they are stupid and lazy...
If there is a way to limit the need for multiple clicks, this method may simplify programing.
Another issue with the validation.
The number of rows down may vary.
Will I have to fill every cell in a sheet to accomidate the DV's
or can a dv be added through vb?

[rory]

Why not use an autocorrect entry? Eg set 'qwe' to correct to 'pfn'. Then you just type qwe and as soon as you hit space, it changes to pfn. Or did I misunderstand?

[mperrah]

does auto correct allow for a target area.
I only need it on Sheet("Data").range("R3:AR" & lastrow)

[rory]

Not exactly, though you could use the SelectionChange event to add/remove the relevant items, using Autocorrect.Addreplacement and Deletereplacement.

[Norie]

mperrah

What is this repetitive task?

Perhaps if you explained that we could suggest another approach.

Taking over the keypad seems a bit much to me.:bug:

[rory]

Taking over the keypad seems a bit much to me
and I hadn't even got around to suggesting a low-level keyboard hook to convert keystrokes as they are made... :)

[mperrah]

post #20 covers most of what I'm doing, but I'll try to phrase it better

Basically on one sheet in the workbook ("Data")
in one range ("R3:AR" & lastrow)
when I type "4" I need a "p"
a normal 4 or keypad 4 or numlock 4

we can have a message box that asks the user where the numbers they plan to use are and toggle on the correct onkey replacement? - not sure how to..

5 = f, 6 = n, 7 = (offset one cell to right)

Background of project
I have a workbook that automates reporting.
We have to fill in a form with info pulled from a website,
then part of the form is a check on an installations (Dish Network)
We Quality check the jobs out in the field
There are 27 items we check.
Rather than printing a form for each job (Usually 20 a week per 18 offices) and writing in 27 squares on each form...
I made a single summary sheet of all 20 jobs I print one of and hand write the 27 items as pass, fail, not applicable, or not scorable.

Then transfering the paper scores to the workbook is were we are here.

I was typing p, f, n, and [blank] on the sheet I printed
but these keys are spread out and I type slow.
So I used the onkey event to type 1, 2, 3, 4 and get p,f,n, or [blank].

It worked for me on my laptop, but sending the file to the other offices, they asked for 4, 5, 6, and 7 on the numberpad keys (they have desktops)
I have a laptop without numkeys, so had not developed for that.
I found a way to work the numpad (Thanks to you)
But I would like both numpad and upper row numbers to work, so if laptop or desktop, one file helps all.

Toggle on and off the key replacement made sense early in the development.
If onkey was active, the file calculated every change and ran slow.
I had it in a worksheet_change event and tried selection_change (worked even slower).

I then learned how to turn calc to manual and most functions spead up.

If you have an idea to accomplish this better, or more efficiently, I am happy to scrap what I have so far...

Maybe having the user prompted to pick what type of numbers they plan to use will help.

We can assign a variable to the input result, and have code set to handle each number key type seperately
maybe a case select, with sub call for each case...

Any ideas are greatly appreciated.

Thanks again.
Mark

[Norie]

Mark

Couldn't you just use a worksheet change event with a select case structure?

If you tried that and it wasn't suitable then perhaps you weren't doing it quite right.

I recall you posted some code previously, including a change event.

I'll take a look back and see if I can see anything that could be causing problems.:)

[Norie]

Mike

I've taken a look back and the main problem I can see is that you aren't turning events off.

If you don't do that and you are changing something on the worksheet the code will repeatedly call itself.

That could explain any slowdown you are experiencing.

I also notice your are using Sendkeys, seemingly to enter edit mode.

Bad idea on 2 points.

1 SendKeys is just flaky :bug:

2 You can't run code when in edit mode.

Maybe you could try something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("R3:AR" & LastRow)

If Target.Count > 1 Then Exit Sub

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.EnableEvents = False

Select Case Target.Value
Case 1
Target = "p"
Case 2
Target = "f"
Case 3
Target = "n"
Case 4
Target.ClearContents
End Select

Application.EnableEvents = True

End Sub
Note I realise the values are hardcoded but that could probably be made more dynamic.

[mperrah]

Thank you, this is much simpler.
One thing,
can the case select fire while typing?

I changed it to move the focus, after enter...
But can we do this with only typing the number keys and nothiong else?
Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow As Long
Dim rng As Range
LastRow = Range("C" & Rows.Count).End(xlUp).Row
Set rng = Range("R3:AR" & LastRow)

If Target.Count > 1 Then Exit Sub

If Intersect(Target, rng) Is Nothing Then Exit Sub

Application.EnableEvents = False
With Target
Select Case Target.Value
Case 1
Target = "p"
.Offset(0, 1).Select
Case 2
Target = "f"
.Offset(0, 1).Select
Case 3
Target = "n"
.Offset(0, 1).Select
Case 4
Target.ClearContents
.Offset(0, 1).Select
End Select
End With

Application.EnableEvents = True

[mperrah]

I don't really have any code running during this process other than entering the values.
I like the simplicity of your code,
just need to enter values with one click or one key stroke.
With the onkey/sendkeys, I can hold down 4 and a p gets entered in each cell as the key repeats. Is there a way to do this with case select,
or incorporate the onkey event with case select ??

[Norie]

Mike

See this comment.


2 You can't run code when in edit mode.

[mperrah]

rory,
whatb is a low-level keyboard hook?
sounds interesting.
I've tweaked the onkey slightly,
this seems to be working on the desktop.
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

[rory]

It's basically intercepting the keyboard strokes at the operating system level and specifying a routine to call whenever a key is pressed. A little OTT for something like this I think.

[mperrah]

Ok
Thanks so much for your help.
I'll mark off solved.
Mark

[schudogue]

I just joined vbaexpress specifically to thank you for this answer on disabling the numpad keys.
Thanks