vba - automatically move to next cell


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

To learn more about macros in general, see:




To learn more about Event Macros (worksheet code), see:


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
  2. Add a new textbox anywhere on your sheet
  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

enter image description here

Right click on textbox and select view code.

enter image description here

use this code there.

enter image description here

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.

Enter Text Result

Author by


Updated on November 28, 2022


  • pexpex223
    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
      Gary's Student about 8 years
      Do you mean without touching the ENTER key ??
    • aelveborn
      aelveborn about 8 years
      You can, but you should not.
    • pexpex223
      pexpex223 about 8 years
      @Gary'sStudent, Yes, that's what i mean.
    • Grade 'Eh' Bacon
      Grade 'Eh' Bacon over 7 years
      While there are some great solutions here, honestly you would gain more by becoming more proficient at data entry in the usual way. Unless you plan on only typing a constant string of 5 digit characters for the next few months, in which case... consider a new career path...
  • pexpex223
    pexpex223 about 8 years
    Hi Gary, This is such an elegant code. I love it! But still, is it possible to automatically move down the mouse pointer? i tried the below code. but I won't be able to select the cell which already have 5 length digits again. SelectionChange(ByVal Target As Range), If Len(Target) = 5 Then, ActiveCell.Offset(1, 0).Select
  • Davesexcel
    Davesexcel about 8 years
    My interpretation of the question is: when the user inputs 5 digits, excel automatically selects the next cell. I suppose it could be done with a textbox change, but that would be a heck of a lot of textboxes. You have to perform some kind of action in order for the worksheet change event to trigger.
  • pexpex223
    pexpex223 about 8 years
    @Davesexcel Is it possible to do it with simply select and change instead of using textbox?
  • Gary's Student
    Gary's Student about 8 years
    @Davesexcel The real problem is triggering an event while the user is in Edit mode.............might be possible with a UserForm.............