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?

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

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!

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

4. Disable the design mode by clicking the triangle with the pen(also in the developer-tool-tab).
5. 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.

Comments

[Gary's Student]

Do you mean without touching the ENTER key ??

[aelveborn]

You can, but you should not.

[pexpex223]

@Gary'sStudent, Yes, that's what i mean.

[Grade 'Eh' Bacon]

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]

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]

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]

@Davesexcel Is it possible to do it with simply select and change instead of using textbox?

[Gary's Student]

@Davesexcel The real problem is triggering an event while the user is in Edit mode.............might be possible with a UserForm.............