Solved

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

Posted on 2010-08-23
21
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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
 
LVL 1

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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