Automatic Tab in Excel after cell entry

[Sypman]

I'm trying to create an Excel document that automatically moves to th
next cell after the user types in a number between 0 and 9. I don'
want the user to have to Tab. Anyone know how to do this? Any hel
would be appreciated. Thanks

 

[Frank Kabel]

Hi
not really possible. You can't execute a macro while in edit mode (that
is while you enter something).

This may be possible using some Windows API calls (I think Harlan Grove
outlines a way some weeks ago but also mentioned that this wouldn't be
a robust solution)

So IMHO your user simply have to use the TAB key

 

[Dave Peterson]

How about an enter?

You could change the direction:
tools|options|Edit|Move Selection after Enter

And if you use the number keypad, you can type your numbers and slide to the
enter key. (It'll become second nature.) This way you can handle any special
(>1 digit numbers) pretty easily.

But if you really wanted to make it so you could just type 1 number (no
corrections & no letters), then you could create a small userform that consisted
of one text box. Double click on the textbox and paste this code in:

Option Explicit
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case 48 To 57
ActiveCell.Value = Chr(KeyAscii)
If ActiveCell.Row > 10 Then
Cells(1, ActiveCell.Column + 1).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Case Else
'do nothing
End Select
KeyAscii = 0

End Sub

Then in a general module, have this little macro that shows the form:

Option Explicit
Sub testme01()
UserForm1.Show
End Sub

select your cell and run the macro that shows the form. And type single digits.

This portion decides the direction to go to next:

If ActiveCell.Row > 10 Then
Cells(1, ActiveCell.Column + 1).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If

You could modify that to go across instead of down.

If ActiveCell.Column > 10 Then
Cells(ActiveCell.Column + 1, 1).Activate
Else
ActiveCell.Offset(0, 1).Activate
End If

David McRitchie has some notes for getting started with macros at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=====
But I wouldn't do it. I make too many typos. I'd use the move selection after
enter stuff and be happy.

 

[tetreaultl]

Hi Dave,

Is there a way to apply the move to the left only for one worksheet and not
to all excel's worksheet?

Louis

 

[Dave Peterson]

You could add some code to some events.

Hit alt-F11 to get to the vbe.
hit ctrl-F4 to view the project explorer (like windows explorer)
Look for your workbook's project
expand it to show ThisWorkbook.
double click on that and paste this into the code window:

Option Explicit
Private Sub Workbook_Activate()
If LCase(ActiveSheet.Name) = lcase(MySheetName) Then
Call enableMovement
End If
End Sub
Private Sub Workbook_Deactivate()
Call resetMovement
End Sub

Then doubleclick on the worksheet that should have the behavior and paste this
in:

Option Explicit
Private Sub Worksheet_Activate()
Call enableMovement
End Sub
Private Sub Worksheet_Deactivate()
Call resetMovement
End Sub


Then rightclick on your project and select Insert|Module. Then paste this in:

Option Explicit
Dim CurMoveAfterReturn As Boolean
Dim CurMoveAfterReturnDirection As Long
Const MySheetName As String = "sheet1"
Sub auto_open()
With Application
CurMoveAfterReturn = .MoveAfterReturn
CurMoveAfterReturnDirection = .MoveAfterReturnDirection
End With

If LCase(ActiveSheet.Name) = lcase(MySheetName) Then
Call enableMovement
End If
End Sub
Sub enableMovement()
With Application
.MoveAfterReturn = True
.MoveAfterReturnDirection = xlToLeft
End With
End Sub
Sub resetMovement()
With Application
.MoveAfterReturn = CurMoveAfterReturn
If CurMoveAfterReturnDirection = 0 Then
CurMoveAfterReturnDirection = xlDown
End If
.MoveAfterReturnDirection = CurMoveAfterReturnDirection
End With
End Sub

Change the name of your sheet (I used Sheet1).

Now back to excel. Save your workbook, close it, reopen it (to make the
auto_open code fire.)

This looks at the user's current setting and keeps track when they change to a
different sheet or a different workbook. And tries to make it move to the left
(did you really mean that???) when you return to that sheet.

And a couple of links to describe events and macros:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi Dave,

Is there a way to apply the move to the left only for one worksheet and not
to all excel's worksheet?

Louis

 

[Dave Peterson]

And one reason I'd never use this routine.

If you change worksheets/workbooks, you're running a macro. And when you run a
macro, lots of nice things can go away--Edit|Undo and in this case, excel's
clipboard gets emptied.

That can be a big old pain.