Can Excel be made to auto-advance to the next cell?

[dneunabe]

Good evening,

Is there a way to make excel advance to next lowest cell automatically once a number of a specifed length (e.g. single digit integers in my case) is entered in a cell? I have large volumes of data to enter--usually integers from 0 to 9--and the extra "enter" keystroke becomes very tedious and increases errors.
Thanks much!
Dan

 

[Yogi Anand]

Hi Dan:

Welcome to MrExcel Board!

Please clarify what you are trying to accomplish with an example or two -- that will help in understanding what you are working with!

 

[Tom Urtis]

No, that is not possible as you describe what you want. The reason is, no VBA action can be triggered while Excel is in Edit mode, which you would be in while you are in the process of entering those numbers in a cell.

What you can do, is create a small userform with a textbox, and have the textbox be coded such that when the nth character is entered (can further be mandated to be a digit), then that value will automatically be entered in the next empty cell, and you can start right away typing the next value in that same text box without missing a beat.

Post back if you get stuck with the userform code, if you decide to go that route.

 

[CraigM]

A slightly different approach, but one that might interest you.
You could use one cell on your sheet (A1 in my following example) to enter one long number which consists off all your data eg "8625317", then use code like the following in the sheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [A1].Address Then
For n = 1 To Len([A1])
Cells(n, 2) = Mid([A1], n, 1)
Next
End If
End Sub

which will split it up into single digits, and list them in column B.

I don't know how applicable this will be to your problem, but it is another idea.

 

[CraigM]

Actually, I've realised my previous post doesn't work to well with large numbers.

 

[CraigM]

Sorry about the stuffing around. The code does work, but you need to format A1 as text. That way you don't get large numbers becoming 1.23456789E+10 etc, which was the problem I encountered.

 

[dneunabe]

I'm a novice to this message board, so please bear with me:

1. I enter large numbers of integers gathered as research data.
2. They usually vary from 0 to 9
3. After entering a number in a cell , I must then hit "enter" to advance to the next empty cell.
4. Thus, to enter one number requires two key strokes.
5. I'm wondering if Excel can be programed to read the proper length of the cell entry (in my case a single integer) and then automatically advance to the next cell down--which is the location of the next entry. So, essentially, I'm asking if Excel can auto-advance Cell B1+1 (for example) once the proper number has been in cell B1--without the enter key.

Hope this helps. It is a a data entry problem.
Thanks much,
dan

 

[SIXTH SENSE]

Hi!
here'a code that will do that.
First dragdown a textbox control from the control toolbox.
then press Alt+F11.
In VBE insert the code below.
Then type any single character in the textbox. it will be entered in the first blank cell in Column A.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> TextBox1_KeyPress(<SPAN style="color:#00007F">ByVal</SPAN> KeyAscii As MSForms.ReturnInteger)
    Range("A65536").End(xlUp).Offset(1, 0).Value = Chr(KeyAscii)
    TextBox1.Value = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

No need to press enter.
Just enter numbers as fast as you can.
Tell me if you can beat excel. ie xcel misses few or one of you inputs. (Just Kidding)
Edit:
Forgot one thing! Exit design mode before you type anything on the
textbox.

 

[Tom Urtis]

So, essentially, I'm asking if Excel can auto-advance Cell B1+1 (for example) once the proper number has been in cell B1--without the enter key.

The answer is No. See my previous post above for more info on why, and a workaround utilizing a userform. Until and unless, as you are typing a value into a cell, you hit the enter key (or the Tab key or an arrow key or click in a different cell to actually enter the value), you are still in Edit mode and Excel does not allow for the kind of behavior to automatically move you to a different cell when a certain number of characters is reached. Even if you used data validation to restrict or specify character length it would still require the Enter key (or entry method) to be invoked, defeating your purpose. A text box with a Change event to recognize character length would be your best option, in a userform.

 

[dneunabe]

Thanks. Got it to work. Learned something from each response . Amazing.