Automatic Carriage Return

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Is there any way to have Excel automatically do a carriage return in A:A
after 4 digits are entered? I want to key in 4 digits in A1 and have it
automatically go to A2, then enter my next 4 digits in A2 and automatically
go to A3....all the way down column A.
 
Not really. As soon as you start editing the value in the cell, pretty much all
macros that do anything won't be running.

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 digits.

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 four
characters.

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

Put this code in a General module. Hit alt-F8 to invoke this macro to show the
tiny userform.

Option Explicit
Sub ShowTheForm()
'Start in column A of the row with the activecell
ActiveCell.EntireRow.Cells(1).Select
UserForm1.Show
End Sub

Then put this code behind the userform:

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

Dim MaxLength As Long

MaxLength = 4

'only digits 0-9
If KeyAscii >= Asc(0) _
And KeyAscii <= Asc(9) Then
Me.TextBox1.Text = Me.TextBox1.Text & Chr(KeyAscii)
If Len(Me.TextBox1.Text) >= MaxLength Then
ActiveCell.Value = Me.TextBox1.Text
ActiveCell.Offset(1, 0).EntireRow.Cells(1).Select
TextBox1.Value = ""
End If
Else
Beep
End If

KeyAscii = 0

End Sub

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
 
Back
Top