Move to next cell automatically after typing one digit

[avi]

Hello,

I'd like to trigger an event that will move automatically the
selection to the next right cell after I have typed one digit only in
the current cell

I am aware of the Worksheet_Change(ByVal Target As Range) event but I
am looking for moving to the next cell without hiting the enter or
Arrow keys

Thank for your help

Avi

 

[Mike H]

Hi,

You can't do that.

All you can do is have the active cell move one to the right when enter is
pressed. if you only want a single character in a cell use data validation.

To set the move direction:-

tools|options edit tab and choose direction.

Mike

Mike

 

[Dave Peterson]

Saved from a previous post:

I would turn
tools|options|edit tab|move selection after enter
to down or right or ...

Then turn number lock on and use the numeric keypad to type your digit

Hitting the enter key on the numeric keypad doesn't seem too bad to me.

Another alternative is to create a tiny userform that just looks for a digit:

Put a single textbox on it (use the X button to close the userform).

Put this code in a General module:

Option Explicit
Sub testme01()
'Start in column A of the row with the activecell
ActiveSheet.Cells(ActiveCell.Row, 1).Activate
UserForm1.Show
End Sub

Add this code to the userform module:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

With ActiveCell
'only digits 0-9
If KeyAscii >= Asc(0) _
And KeyAscii <= Asc(9) Then
.Value = Chr(KeyAscii)
'A:E, then down a row
If ActiveCell.Column = 5 Then
ActiveCell.EntireRow.Cells(1).Offset(1, 0).Activate
Else
.Offset(0, 1).Activate
End If
Else
Beep
End If
End With

KeyAscii = 0
TextBox1.Value = ""

End Sub


This code goes from A:E then next row, column A.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Debra Dalgleish has some getstarted instructions for userforms at:
http://contextures.com/xlUserForm01.html