Solved

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

Posted on 2010-08-23
21
356 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

914 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

17 Experts available now in Live!

Get 1:1 Help Now