Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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.
0
gordonwwaugh
Asked:
gordonwwaugh
1 Solution
 
calacucciaCommented:
One method is putting this procedure in the Worksheet SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
If Target.Column = 4 Then
        Me.Cells(Target.Row, 1).Activate
ElseIf Target.Column = 3 Then
        Me.Cells(Target.Row - 1, 4).Activate
ElseIf Target.Column = 2 Then
        Me.Cells(Target.Row - 1, 3).Activate
ElseIf Target.Column = 1 Then
        Me.Cells(Target.Row - 1, 2).Activate
End If
    Application.EnableEvents = True
End Sub
0
 
calacucciaCommented:
Adding to this, to prvent this from happening outside your goal range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iSect As Range
Set iSect = Intersect(Target, Me.Range("A1:D5000"))
If iSect Is Nothing Then Exit Sub
    Application.EnableEvents = False
If Target.Column = 4 Then
        Me.Cells(Target.Row, 1).Activate
ElseIf Target.Column = 3 Then
        Me.Cells(Target.Row - 1, 4).Activate
ElseIf Target.Column = 2 Then
        Me.Cells(Target.Row - 1, 3).Activate
ElseIf Target.Column = 1 Then
        Me.Cells(Target.Row - 1, 2).Activate
End If
    Application.EnableEvents = True
End Sub
0
 
MWGainesJRCommented:
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

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
2toriaCommented:
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.

Matt
0
 
Patrick MatthewsCommented:
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
0
 
calacucciaCommented:
This to capture enter key from numerical keypad or regular Enter key (tilde)

First, assign your keys on Workbook open

Private Sub Workbook_Open()
Application.OnKey "{ENTER}", "Reposition"
Application.OnKey "~", "Reposition"
 
 End Sub


This code in a regular VBA Module in your workbook

Sub Reposition()
Dim Target As Range
Set Target = ActiveCell
If Target.Column = 4 Then
        Target.Parent.Cells(Target.Row + 1, 1).Activate
ElseIf Target.Column = 3 Then
        Target.Parent.Cells(Target.Row, 4).Activate
ElseIf Target.Column = 2 Then
        Target.Parent.Cells(Target.Row, 3).Activate
ElseIf Target.Column = 1 Then
        Target.Parent.Cells(Target.Row, 2).Activate
End If

End Sub

0
 
calacucciaCommented:
You should, by the way, re-assign both Keys at the end (for example on your Workbook BeforeClose event by this type of code (just not specifying anything will reset it)

Application.OnKey "{ENTER}"
Application.OnKey "~"
0
 
gordonwwaughAuthor Commented:
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'.
0
 
TracyVBA DeveloperCommented:
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.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Cells(lastRow, 1).Value = TextBox1.Value
    TextBox1.Value = ""
    
End Sub

Open in new window

Book2.xls
0
 
calacucciaCommented:
And finally I see I miissed the Single Digit Entry :-)

0
 
gordonwwaughAuthor Commented:
Why do I need to reassign the ENTER key if I will never be pressing the ENTER key? I will only be typing numbers on the numeric keypad.
0
 
calacucciaCommented:
Does attached workbook work as described for the workbook_SelectionChange event?
Try enter on Sheet1


Book1.xls
0
 
calacucciaCommented:
Yep, gordon, I just figured that out, that you wanted numpad only.
I'm trying to find a solution right now.
0
 
gordonwwaughAuthor Commented:
Aha. Yes Workbook_SelectionChange works now. I had inadvertantly put it in the Workbook object instead of the Sheet1 object. That's probably why "Me" was invalid.
0
 
calacucciaCommented:
Now, with the keys.

For the moment not with NumPad yet, only with the standard Keys on top of main keyboard part.

But it works already :-)

Put this in  Workbook object.
Run it once to launch the assignment.

Private Sub Workbook_Open()
Application.OnKey "1", "'Module1.MyProcedure 1'"
Application.OnKey "2", "'Module1.MyProcedure 2'"
Application.OnKey "3", "'Module1.MyProcedure 3'"
Application.OnKey "4", "'Module1.MyProcedure 4'"
Application.OnKey "5", "'Module1.MyProcedure 5'"
Application.OnKey "6", "'Module1.MyProcedure 6'"
Application.OnKey "7", "'Module1.MyProcedure 7'"
Application.OnKey "8", "'Module1.MyProcedure 8'"
Application.OnKey "9", "'Module1.MyProcedure 9'"
Application.OnKey "0", "'Module1.MyProcedure 0'"
End Sub

Then put this in MOdule 1

Sub MyProcedure(m As Integer)
ActiveCell.Value = m
If ActiveCell.Column = 4 Then
    ActiveCell.Offset(1, -3).Activate
ElseIf ActiveCell.Column < 4 Then
    ActiveCell.Offset(0, 1).Activate
End If
End Sub

Test.

Also add this somewhere so you can re-assign the 1-10 keys to standard.

Application.OnKey "1"
Application.OnKey "2"
Application.OnKey "3"
Application.OnKey "4"
Application.OnKey "5"
Application.OnKey "6"
Application.OnKey "7"
Application.OnKey "8"
Application.OnKey "9"
Application.OnKey "0"
0
 
calacucciaCommented:
With the numpads as well

Private Sub Workbook_Open()
Application.OnKey "1", "'Module1.MyProcedure 1'"
Application.OnKey "2", "'Module1.MyProcedure 2'"
Application.OnKey "3", "'Module1.MyProcedure 3'"
Application.OnKey "4", "'Module1.MyProcedure 4'"
Application.OnKey "5", "'Module1.MyProcedure 5'"
Application.OnKey "6", "'Module1.MyProcedure 6'"
Application.OnKey "7", "'Module1.MyProcedure 7'"
Application.OnKey "8", "'Module1.MyProcedure 8'"
Application.OnKey "9", "'Module1.MyProcedure 9'"
Application.OnKey "0", "'Module1.MyProcedure 0'"
Application.OnKey "{96}", "'Module1.MyProcedure 0'"
Application.OnKey "{97}", "'Module1.MyProcedure 1'"
Application.OnKey "{98}", "'Module1.MyProcedure 2'"
Application.OnKey "{99}", "'Module1.MyProcedure 3'"
Application.OnKey "{100}", "'Module1.MyProcedure 4'"
Application.OnKey "{101}", "'Module1.MyProcedure 5'"
Application.OnKey "{102}", "'Module1.MyProcedure 6'"
Application.OnKey "{103}", "'Module1.MyProcedure 7'"
Application.OnKey "{104}", "'Module1.MyProcedure 8'"
Application.OnKey "{105}", "'Module1.MyProcedure 9'"

End Sub

Other procedure remains the same.

You might want to add Enter Keys as well as explained above.
0
 
calacucciaCommented:
This will give you unexpected behaviour for cells outside your range or on other workbooks or worksheets.

I think the trick is to re-assign the standard way of handling the keys on the Worksheet Deactivate event or/and the workbook Deactivate.

You'll need to re-assign everything on the Worksheet & workbook Activate event if you do that.

But I think you got the picture.
0
 
gordonwwaughAuthor Commented:
Excellent! It works perfectly!

By the way, I deleted the Sub Reposition procedure and the Worksheet_SelectionChange procedure you provided earlier because your new procedures replace them.
0
 
gordonwwaughAuthor Commented:
I will be using this VBA code a lot--whenever I have to enter a lot of single-digit numbers in a spreadsheet.
0
 
calacucciaCommented:
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^^
0
 
gordonwwaughAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now