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.

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

Searching for Paragraph Formatting

You can use the Find and Replace capabilities of Word to search for a wide variety of information. One thing you can look for ...

Discover More

Sequential Page Numbers Across Worksheets

How do you want your page numbers to appear on your printed worksheets? Chances are good that you want them to be sequential, ...

Discover More

Specifying a Location To Save Automatic Backup Files

Word allows you make two types of automatic backups of your documents. One is a copy of the file before you start making ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Seeing Excel's Program Window

Have you ever opened Excel to find that the window you saw yesterday is not the same as it is today? Sometimes, for various ...

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 two ...

Discover More

Setting the Width for Row Labels

Excel displays, by default, a row label or heading at the left side of each row on the screen. As you scroll down the screen, ...

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 8Mpixels. 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 6 - 3?

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.