Enter value in cell, move to next cell without Enter or Tab

November 17, 2013 at 05:58:57
Specs: Windows 7
Hi Guys,
Have been looking to find the answer to this, but am getting no-where. Here is what I am trying to do.

I have a column of cells, from C1 to C9
I enter a value of between 0 and 9 in C1
Without hitting (Enter) or (Tab), we automatically jump to C2
I enter a value of between 0 and 9 in C2
Without hitting (Enter) or (Tab), we automatically jump to C3

and so on until the last value is entered into C9

I then click on a Forms Button and the information in C1 - C9 is pasted to another worksheet,
and the information in C1 - C9 is cleared ready, for the next lot of values to be entered.

Has any one got any ideas of how to go about this?

See More: Enter value in cell, move to next cell without Enter or Tab

Report •

November 17, 2013 at 21:51:48

It may be because of the cell format. Try to make the cell format into number and then try to enter as much as you can.

Report •

November 18, 2013 at 05:25:48
Excel needs some way of knowing when you are done entering data in the cell. How would Excel know whether you wanted to enter 1 digit or 50?

Something has to tell Excel that you are done entering data, be it Enter, Tab or an arrow key.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 18, 2013 at 05:52:15
Thanks for responding.
Your answer is what I have found in other Forums.
However if the value of the entry is known, ie between 0 and 9 inclusive, is there someway around this. That a single entry (digit) has been made, and therefore move to the next required cell

At the moment I am using data validation to ensure that the figure entered in the cells is between the criteria 0 to 9 inclusive, and then (Tab)

Report •

Related Solutions

November 18, 2013 at 06:29:57
Option Explicit

'=================== [ S I N G L E - D I G I T   E N T R Y ] ===================
' Other code may be added to each module as needed.

Private Sub Worksheet_Activate()
    CheckSingleDigitEntry ActiveCell
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  CheckSingleDigitEntry Target
End Sub

Private Sub Worksheet_Deactivate()
End Sub
'================== [ / S I N G L E - D I G I T   E N T R Y ] ==================


Option Explicit

Dim bInit           As Boolean
Dim bIsOn           As Boolean

Sub CheckSingleDigitEntry(ByVal rSelection As Range)
    ' shg 2010, 2012

    ' This must exist as a named range with sheet scope for each
    ' worksheet that uses single-digit entry
    Const sSDE      As String = "rgnSingleDigitEntry"
    Dim rSDE        As Range
    Dim bWasOn      As Boolean

    ' If the name does not exist, the sub should never have been
    ' called, but hey, we'll check:
    On Error Resume Next
    Set rSDE = rSelection.Worksheet.Names(sSDE).RefersToRange
    If rSDE Is Nothing Then Exit Sub
    On Error GoTo 0

    ' Reset in the event the VBE has been reset (because the OnKey
    ' assignments will persist)
    If Not bInit Then
        bInit = True
    End If

    If rSelection.Cells.Count > 1 Then
        ' Don't want single-digit entry if more than one cell is selected

        bWasOn = bIsOn
        bIsOn = Not Intersect(rSelection, rSDE) Is Nothing

        If bIsOn And Not bWasOn Then
        ElseIf Not bIsOn And bWasOn Then
        End If
    End If
End Sub

Sub SetSingleDigitEntry()
    Dim iDigit      As Long

    For iDigit = Asc("0") To Asc("9")
        Application.OnKey Chr(iDigit), "'SingleDigitEntry " & Chr(iDigit) & "'"
    Next iDigit
End Sub

Sub ResetSingleDigitEntry()
    Dim iDigit      As Long

    For iDigit = Asc("0") To Asc("9")
        Application.OnKey Chr(iDigit)
    Next iDigit
    bIsOn = False
End Sub

Sub SingleDigitEntry(i As Long)
    With ActiveCell
        .Value = i
    End With
End Sub

Hi, I have found this out there. Could you possibly break this down a bit more for me and explain how I apply this to my worksheet

Report •

November 18, 2013 at 06:48:52
You could enter all 9 digits in cell C1 or maybe in some type of input box,
then have Excel populate the other cells by breaking apart the number.

It could probably be done with formulas, but I think VBA would be quicker.



Report •

November 18, 2013 at 10:33:13
Where did you find that code?

Parts of the code are triggered by the activation of a sheet and parts are triggered when the sheet is deactivated. There was nothing mentioned in your original post about activating/deactivating a sheet. I don't know what the purpose of the code is or if it can be modified for your use.

Perhaps some background on exactly what that code was being used for would help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

November 18, 2013 at 13:17:20
This page looks like it may have something you could work with.




Report •

November 19, 2013 at 09:26:20
Hi Guys,
Thanks for the responses.

DerbyDad03 - the above code was found on the mrexcel forum written by user shg However I cannot know find that code again on there thread board.
The coding is way above my ability to understand the ifs, whats and whys of what the coding will do and how it goes about it. As I understood the descriptive question on the thread, you should be able to enter a single digit between 0 and 9 inclusive and the cursor will automatically move to the next cell. As to activating and deactivating the worksheet, this is not something I think I need.

Mike - same sort of thing, the link you supplied showed coding that I will struggle to understand.

Unless there is something out there that with my limited level of understanding code, I could follow and manipulate, then I think I will leave it as I have it. Data validated the cells so only values between 0 and 9 incl. can be entered. Obviously there is the additional key stroke of TAB or ENTER, but it does what is required.

Thanks for your assistance with this, can't win them all

Report •

November 19, 2013 at 14:50:54
Have you thought about using a User Form with just one field
then have VBA split apart the data, since you want to post the data to
another sheet anyways?

With the User Form, I don't know how difficult it would be to split the data apart
and place it into the cells you want, but DerbyDad03 would probably have a better
understand of it then me.

Here is a link for creating a User Form:


Also, this is a partial solution using a formula.

You enter your 9 digits into cell A1
In cell C1 enter the formula: =MID($A$1,ROW(),1)
drag down 8 rows and C1 thru C9 should have a single digit

Now all you will need to do is create some VBA to move the data
from C1 thru C9 to your second screen and zero out cell A1
and attach it to a button.



message edited by mmcconaghy

Report •

Ask Question