number pad key id?

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.

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?

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?

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

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

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:

Quote:

---

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... :)

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

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.:)

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.
[vba]
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[/vba]
Note I realise the values are hardcoded but that could probably be made more dynamic.

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?
[vba]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[/vba]

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 ??

Mike

See this comment.

Quote:

---

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

---

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

[/VBA]

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.

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

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