Auto move to next cell

hamptongolfer11 · Jan 16th 2022

Hi, I am entering single digit numbers into a spreadsheet. After entering each number, I would like the cell to jump to the right without pressing enter, tab or right arrow key. I have done multiple searches on google but can’t find a solution.

I have attached a demo sheet to show what I would like to do. The Golf Canada website allows you to do this when entering induvial scores for handicap purposes. Perhaps they are not using excel to do this.

I have to input 100 to 120 eighteen hole rounds each week. Any help would be appreciated. Thanks.

**royUK** · Jan 17th 2022

You simply use the Enter Button after adding the entry.

hamptongolfer11 · Jan 17th 2022

Quote from royUK

You simply use the Enter Button after adding the entry.

Thanks Roy, but that is what I am trying to avoid.

tr1face · Jan 17th 2022

Hey Hamp,

Until you're pressing Enter you're still gonna be in Edit Mode, so I don't think it's possible.

You could use another cell on your sheet to enter one long number which consists of all your data, then split it up into single digits, listing them in desired rows

hamptongolfer11 · Jan 17th 2022

Quote from tr1face

Hey Hamp,

Until you're pressing Enter you're still gonna be in Edit Mode, so I don't think it's possible.

You could use another cell on your sheet to enter one long number which consists of all your data, then split it up into single digits, listing them in desired rows

This may work but I am not sure of the process to "split it up into single digits, listing them in desired rows". I am fairly new to macros.

rollis13 · Jan 17th 2022

Hi to all.

hamptongolfer11, have a try with what I came up.

I'm using function OnKeys to launch a macro to show the key pressed and add a Tab key. This macro is only for 0 to 9, nothing to do with Numeric keypad since they have a different coding and to all the other keys of the keyboard. If needed you can implement them (and fix any other bug ).

To move through the cells the best I could do was to lock the other cells and then lock the sheet. Doing so there would be no reason to use "If Not Intersect..." in the event Worksheet_SelectionChange but since it's a couple of hours I'm banging my head there it is and there it remains .

There also is a problem with NumLock key but that's a Windows x64 problem, you can see my workaround.

So, macro Worksheet_SelectionChange in the module of the sheet and the other macros in a standard module:

Code

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim KeysArray As Variant
    Dim MacrosArray As Variant
    Dim x      As Integer
    Dim Key    As Variant
    KeysArray = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
    MacrosArray = Array("zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine")
    If Not Intersect(Target, Range("C5:K19,M5:U19")) Is Nothing Then
        For x = 0 To 9
            Application.OnKey KeysArray(x), MacrosArray(x) 'enable OnKey function
        Next x
    Else
        For Each Key In KeysArray
            Application.OnKey Key                 'disable OnKey function
        Next Key
    End If
    '(only x64) restore the condition of NumLock since if active it is disabled due to a bug
    DoEvents
    SendKeys "{NUMLOCK}{NUMLOCK}"
End Sub

Display More

Code

Option Explicit
Sub zero()
    ActiveCell = Chr(48): Application.SendKeys ("{TAB}")
End Sub
Sub one()
    ActiveCell = Chr(49): Application.SendKeys ("{TAB}")
End Sub
Sub two()
    ActiveCell = Chr(50): Application.SendKeys ("{TAB}")
End Sub
Sub three()
    ActiveCell = Chr(51): Application.SendKeys ("{TAB}")
End Sub
Sub four()
    ActiveCell = Chr(52): Application.SendKeys ("{TAB}")
End Sub
Sub five()
    ActiveCell = Chr(53): Application.SendKeys ("{TAB}")
End Sub
Sub six()
    ActiveCell = Chr(54): Application.SendKeys ("{TAB}")
End Sub
Sub seven()
    ActiveCell = Chr(55): Application.SendKeys ("{TAB}")
End Sub
Sub eight()
    ActiveCell = Chr(56): Application.SendKeys ("{TAB}")
End Sub
Sub nine()
    ActiveCell = Chr(57): Application.SendKeys ("{TAB}")
End Sub

Display More

hamptongolfer11 · Jan 17th 2022

Thanks Rollis for all your work. The example you attached works exactly as I want it to. I will have to try and find a work around to use the keypad. I am 5 times slower using the # keys than the keypad. Appreciate your effort.

rollis13 · Jan 17th 2022

Quote from hamptongolfer11

I will have to try and find a work around to use the keypad

I think it will be useless if you have a Windows x64, the NumLock bug I indicated will 'get you trouble'.

This is a long time issue so I don't understand why it never has been fixed. Don't Win x64 users use SendKeys anymore ?

Auto move to next cell

Participate now!

Share