How do I automatically move to the next cell when I enter a single-digit number?

I want to enter a lot of single-digit numbers (1 through 4) in a spreadsheet. I can save a lot of time if I do not have to hit the Enter or Tab key after each number. I can do this in Access using VBA by capturing the keypress event and setting focus to the next field. I'm not very familiar with Excel VBA, however.

I will be entering the numbers in Cells A1:D5000. There are also a lot of cells that I want to leave blank, so I would like to just hit the Tab key (or any other key) to leave a cell blank.

After I enter a number in column D, I want Excel to move back to column A (and down 1 row). If I need to select the rectangle of cells I am entering values into to do this, that's fine.

here, the following code will do everything you want (you'll have to hit Enter after inserting the number). To skip a cell just press the space bar.
Put the following code in the sheet's module in VBA

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = " " Then
Target.Clear
End If
If Target.Column > 4 Then
Exit Sub
Else
If Target.Column < 4 Then
Target.Offset(0, 1).Select
Else
If Target.Column = 4 Then
Cells(Target.Row + 1, 1).Select
End If
End If
End If
End Sub

Unfortunately to my knowledge Excel doesn't capture keypress events within cells in the way you'd like. Your best option might be to create a userform which allowed data input and populated the cells for you.

gordonwwaugh,Excel does not really have a KeyPress or KeyDown event, although ActiveX textboxes may. In any event, once you start entering a number in a cell, the cell goes into edit mode, and you cannot call VBA code whilst edit mode is engaged.Have you thought about using Excel's data form? It is useful for data entry...Patrick

I'm going to need a little handholding getting the calacuccia code in the right place.

I placed it in the Sheet1 object in the VBA editor. In Access, I would first have to Display the Worksheet_SelectionChange event in the Properties sheet. Then click the event which would take me to the VBA Editor. That process would tie the VBA code to the event. Maybe I don't have to do that in Excel. I hope not, because I don't see any list of events in the Properties sheet.

When I compile the code, it does not like the "Me" object. "Invalid use of 'Me'.

Try something like this using a text box. Not exact to your request, but you get the idea. Start typing it letters into the text box and they'll be added to column A. The others have shown you how to move from A-D and back to A already.

Glad you'll be using it often :-) Hopefully without any major negative events, like somebody who per accident got your workbook_Open code suddenly not seeing any expected results from his numpad action^^

Good point. Perhaps I should trigger a message box when the workbook opens that asks the user whether she wants to use single-digit entry. I will also provide some way to specify the column range rather than hard-coding it. Perhaps the column range can be specified on another sheet.

A little background as to how I came to I design this code:
Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.”
David Miller (dlmille)
It was one of those days…
I wa…

The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels.
Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…