keystroke event

[av8tordude]

I would like to display a form when the user presses any number key 0-9. Using Worksheet_Change is not a feasible option because I'm trying to prevent the user from typing in a number in the cell. Can someone assist. thx

[Bob Phillips]

Capture the value in the cell before they enter anything using SelectionChhange, then re-instate it afterwards if they type 0-9.

[av8tordude]

Can you give me an example code? thx

[Kenneth Hobs]

If a single key press then use onkey. IF it is after another key press, then you need other methods. I suspect that if we knew more, we could make a better solution.

Sub t()
Dim i As Integer
For i = 0 To 9
Application.OnKey i, "ShowUserForm"
Next i
End Sub

Sub ShowUserForm()
UserForm1.Show
End Sub

[Bob Phillips]

Private prevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("H1")) Is Nothing Then

If IsNumeric(Target.Value) Then

If Target.Value >= 0 And Target.Value <= 9 Then

Target.Value = prevVal
lmyForm.Show
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
prevVal = Target.Value
End Sub

[av8tordude]

Thank Kenneth & XLD, but where would I put this routine so it would be trigger when the user begins to type a number?

[Kenneth Hobs]

Since xld's routine goes in a worksheet object, right click your worksheet's tab, View Code, and paste xld's code.

Mine goes in a Module. Play the t routine or call t from ThisWorkbook object's Open event. Mine will do it for all sheets. Mine needs more tweaks to disable those key triggers and reenable when switching workbooks.