# vba - automatically move to next cell

17,241

## Solution 1

First format column A to Text

Then enter the following Event Macro in the worksheet code area:

``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, L2 As Long, Leftover As Long
If Target.Count > 1 Then Exit Sub
Set A = Range("A:A")
If Intersect(Target, A) Is Nothing Then Exit Sub
v = Target.Text
L2 = Len(v) / 5
Leftover = Len(v) - 5 * L2
j = 1

Application.EnableEvents = False
For i = 1 To L2
Target.Offset(i - 1, 0).Value = Mid(v, j, 5)
j = j + 5
Next i
If Leftover = 0 Then
Else
Target.Offset(L2, 0).Value = Mid(v, j)
L2 = L2 + 1
End If
Target.Offset(L2, 0).Select
Application.EnableEvents = True
End Sub
``````

Then click anywhere in column A and start typing digits or letters. When you are done (or get tired) touch the Enter key.

Your data will end up distributed in sets of 5 downwards with the cursor in the first cell below the entered data.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

## Solution 2

here is an other option that might solve your problem.

1. Open the developer-tool-tab
3. Add the following code by doubleclicking the textbox.

```Private Sub TextBox1_Change() If Len(TextBox1.Value) = 5 Then ActiveCell.Value = TextBox1.Value ActiveCell.Offset(1, 0).Activate TextBox1.Value = "" TextBox1.Activate End If End Sub ```

1. Disable the design mode by clicking the triangle with the pen(also in the developer-tool-tab).
2. Make sure you're in the right cell and then start typing in the textbox.

cya Amnney

## Solution 3

Yep a userform or even an ActiveX textbox could work.

Insert Textbox

Right click on textbox and select view code.

use this code there.

Or copy and paste the code, Make sure textboxes are the same name as in the code

``````Private Sub TextBox1_Change()
If Len(Me.TextBox1) = 5 Then
Me.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Me.TextBox1.Value
Me.TextBox1.Text = ""
End If
End Sub
``````

Start typing in textbox, as soon as you have 5 digits, it will place the text into the 1st empty cell in Column A and clear the textbox.

Share:
17,241
Author by

### pexpex223

Updated on November 28, 2022

• pexpex223 6 months

I have to enter lots of 5-digits set numbers (e.g. 12345, 23456)into column A.

Is there an way to automatically move to next row on column A as soon as I enter 5-digits number in my activate cell?

• Gary's Student about 8 years
Do you mean without touching the ENTER key ??