Saving Movement on Enter with a Workbook

by Allen Wyatt
(last updated April 19, 2014)

10

Kathy uses several Excel workbooks daily. In some, she wants the cursor to move down after entering the data and in others she wants the cursor to move right. She wonders if there is a way to save the cursor direction with each individual workbook.

The direction in which the selected cell moves after you press Enter is determined on an application level by following these steps:

  1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and Excel 2013 display the File tab of the ribbon and then click Options.)
  2. At the left of the dialog box click Advanced. (See Figure 1.)
  3. Figure 1. The advanced options of the Excel Options dialog box.

  4. Under Editing Options, make sure that the checkbox for "After pressing Enter, move selection" is checked (it should be by default).
  5. Using the Direction drop-down list, change the direction as desired. Changing the direction affects how Excel behaves in all workbooks.
  6. Click on OK.

Since it is handled at an application level, the setting is not stored with individual workbooks. You can, however, create a macro that can be stored with the workbook, and the macro can modify the direction. Ideally, the macro would modify the direction when a workbook was activated and restore the direction when the workbook was deactivated—meaning, when a different workbook was activated or the program exited.

Public MouseDirection As Long

Private Sub Workbook_Activate()
    MouseDirection = Application.MoveAfterReturnDirection
    Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Workbook_Deactivate()
    Application.MoveAfterReturnDirection = MouseDirection
End Sub

These are actually two macros and they should be added to the ThisWorkbook object in the VBA Editor. As shown, theWorkbook_Activate macro sets the direction to be towards the right after Enter is pressed. If you prefer, you could replace xlToRight with any of these: xlDown, xlUp, or xlToLeft.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13073) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Getting Word to Remember the Default Date and Time Format

One way to insert the current date into your document is to use the Date and Time dialog box. The Default button in the ...

Discover More

Noting Inactivity within a Timeframe

There are many times when you are creating a worksheet that you need to analyze dates within that worksheet. Once such ...

Discover More

Automatically Applying Custom Styles to Footnotes

Word is great in that it allows you to create styles that define how you want your text to appear. If you spend a great ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Going to the Corners of a Selected Range

When you select a range of cells (particularly if it is a large range of cells), you may not be quite sure if you've ...

Discover More

Reference Shortcut

Need to modify how a cell reference, in a formula, is constructed? The shortcut described in this tip will help you step ...

Discover More

Shortcut to Move between Two Worksheets

Moving between to adjacent worksheets is easy; Excel provides a shortcut key to do the trick. If you want to move between ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 7?

2016-05-18 22:05:14

Alan

For everyone screaming about a simpler way to move cells, you are missing the point. While you are so very correct concerning hot keys, those hot keys do not exist on a 10key! This question comes from someone who has to enter lots of numbers and has to move across columns to complete a job, but we do not want right cell movement to be the default for every workbook for ever and ever!


2015-08-06 12:29:03

Matt

You are perhaps missing the purpose of this macro - it actually works very well with some automated devices (e.g. thickness measuring equipment) where the only 'button' on the device acts as a virtual enter key. This macro allows you to transform and input data in a sideways fashion from these kinds of devices instead of always down. Sometimes it's not as simple as "Just use the arrow keys".

Regards,
Matt


2015-02-21 09:37:34

juraj

I have following problem.
If I am editing the cell end then press enter, the editing process finished but selected cell stay the same. Just after second press of enter the selected cell moves in defined directions. So I have to press enter twice. Can anybody help?


2014-04-22 02:37:05

Rudra Sharma

Hi Guys,
In order to move right simply press tab and to move left press Shift + Tab.Or if you are not in editing mode you can simply use arrow keys as discussed in some of the comments.
With Regards
Rudra


2014-04-20 10:18:06

Lanier

Thanks, Chuck. That was what I meant, but perhaps didn't state clearly enough. I use the arrow keys INSTEAD of the Enter keys.

You also raise a good point about having to use the Enter key in a few cases, such as editing a cell. That is why I also change the default move upon pressing Enter.


2014-04-19 19:30:33

Peter Atherton

Dave, you write them in the Visual Basic Editor. Press ALT + Fll to open the editor. Generally macros are entered in a module (Insert, Module) but un this case the macros go in the workbook module. To access this choose View, Project. You will see a list of the worksheets in your book followed by an Excel Icon. Double click this to open the module.

You will see two dropdown boxes. Click this and choose Workbook. Then click the right dropdown to see a list of built-in options. As some of these need to specifiy variables it is always better to use this method when starting writing a Workbook or a Worksheet event macro.

HTH
Peter


2014-04-19 15:30:26

Chuck Trese

@Lanier,
Actually, there is one thing even easier.......

@all,
It is also possible to simply press the arrow key INSTEAD OF the Enter key, to tell Excel which direction to go.

However, this does not work if you're entering data by using the formula bar or doing "in-cell editing" (in those cases, the arrow keys are already used for moving around inside the cell).


2014-04-19 13:02:49

Lanier

Why not just take the simple approach, as I do. In different spreadsheets I want the cursor to move in different directions, so I just set the default to not move after pressing Enter (do this by un-checking the box for cursor movement in the dialog box described above) so it will stay in the same cell if you press Enter. Then I simply use the arrow keys instead of Enter after putting data in a cell. If I want to move the cursor down, I press the down arrow, etc.

Simple, no macros to write. And I can move the cursor in any direction I want, even back to the previous cell if I want. I can customize the direction with each data entry. What could be easier than that.


2014-04-19 11:05:23

Dave

What I would like to know:

I'm sitting in front of my PC with my spreadsheet open, what do I do now to create the macro?


2014-04-19 11:02:37

Dave

Must admit I'm a novice, but, where di I go to enter this macro?
And, exactly what do I type from the info above?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.