Solved

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

Posted on 2010-08-23
21
354 Views
Last Modified: 2012-05-10
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
Comment
Question by:gordonwwaugh
21 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505323
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505337
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33505345
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
 
LVL 8

Expert Comment

by:2toria
ID: 33505372
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33505381
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505432
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505518
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
 

Author Comment

by:gordonwwaugh
ID: 33505538
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
 
LVL 24

Expert Comment

by:broomee9
ID: 33505544
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505611
And finally I see I miissed the Single Digit Entry :-)

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:gordonwwaugh
ID: 33505612
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505651
Does attached workbook work as described for the workbook_SelectionChange event?
Try enter on Sheet1


Book1.xls
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505656
Yep, gordon, I just figured that out, that you wanted numpad only.
I'm trying to find a solution right now.
0
 

Author Comment

by:gordonwwaugh
ID: 33505689
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
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33505772
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505803
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
 
LVL 17

Expert Comment

by:calacuccia
ID: 33505836
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
 

Author Comment

by:gordonwwaugh
ID: 33505910
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
 

Author Closing Comment

by:gordonwwaugh
ID: 33505937
I will be using this VBA code a lot--whenever I have to enter a lot of single-digit numbers in a spreadsheet.
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33506146
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
 

Author Comment

by:gordonwwaugh
ID: 33506205
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now