CODEWITHSUNDEEP
vbaexcel
pexpex223
pexpex223

Reputation: 371

vba - automatically move to next cell

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?

Upvotes: 1

Views: 20637

Answers (3)

Amnney
Amnney

Reputation: 48

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

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

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

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

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!

Upvotes: 3

Related Questions