Change the Behavior of the Enter Key in Excel 97, 2000, 2002, 2003, and 2007

by David Hakala

When you press the Enter key in an Excel worksheet, it normally drops you down to the next cell in the column. But it doesn’t have to be that way. Excel allows you to specify to which cell the selection goes after you press the Enter key, or whether the selection remains unchanged.

Follow these steps if you are using an Excel version earlier than 2007:

Click Tools, then Options.

Select the Edit tab from all those bewildering tabs.

If you don’t want the selection to change, clear the Move Selection After Enter box. Otherwise, make sure it’s checked.

Use the Direction drop-down list to specify the direction the selection should move after Enter is pressed.

If you are using Excel 2007, the steps are slightly different:

Click the Office button and then Excel Options down at the bottom.

Click Advanced and you will see what to do:

This technique applies to all Excel workbooks. If you want to change the Enter key’s behavior on a single workbook but leave the default setting alone on all others, you can use a macro saved in that particular workbook.

The following code sets the direction in which the selection moves after Enter is pressed to “up one cell”:

Option Explicit
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
bMove = Application.MoveAfterReturn
lMoveDirection = Application.MoveAfterReturnDirection

Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlUp
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Application.MoveAfterReturn = bMove
Application.MoveAfterReturnDirection = lMoveDirection
End Sub

You can change “x1up” to another direction constant, i. e., xlDown, xlToLeft, or xlToRight.

To add this code to a worksheet, press Alt-F11 to open the VBA editor. Select the thisWorkbook object in the editor and paste the code into the editing window. Change the direction as needed before saving.

You also need to add this code to the Declaration section of any module:

Option Explicit
Public lMoveDirection As Long
Public bMove As Boolean

David Hakala has perpetrated technology tutorials since 1988 in addition to committing tech journalism, documentation, Web sites, marketing collateral, and profitable prose in general. His complete rap sheet can be seen at http://www.linkedin.com/in/dhakala