vba - automatically move to next cell

Question

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?

You can, but you [should not](http://stackoverflow.com/q/10714251/11683). — GSerg, Mar 15 '15 at 17:19
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... — Grade 'Eh' Bacon, Oct 29 '15 at 12:55

score 3 · Answer 1 · answered Mar 15 '15 at 18:13

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:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
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:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

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

and

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

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

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

Macros must be enabled for this to work!

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 — pexpex223, Mar 15 '15 at 18:27
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. — Davesexcel, Mar 15 '15 at 18:27
@Davesexcel Is it possible to do it with simply select and change instead of using textbox? — pexpex223, Mar 15 '15 at 18:31
@Davesexcel The real problem is triggering an event while the user is in Edit mode.............might be possible with a UserForm............. — Gary's Student, Mar 15 '15 at 18:31

score 2 · Answer 2 · answered Mar 15 '15 at 19:18

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

score 2 · Answer 3 · edited Oct 29 '15 at 12:51

here is an other option that might solve your problem.

Open the developer-tool-tab
Add a new textbox anywhere on your sheet
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

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

cya Amnney

vba - automatically move to next cell

3 Answers3