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

Quickly Changing Columns

One of the tools on the Formatting toolbar is the Columns tool. You can use this tool to make quick changes to the number of ...

Discover More

Editing Graphic Objects

Want to change the way that a graphics object appears in your worksheet? You need to edit it, then, using the techniques ...

Discover More

Setting Print Titles

Excel allows you to specify certain rows or columns that will be repeated on the pages of a printout. Here's how to set those ...

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)

Changing Your Name

One of the many pieces of information that Excel keeps track of is your name. If you want to change your name for Excel's ...

Discover More

Drop-Down List Font Sizes

Excel has several features that cannot be customized. The font size in the drop-down lists is one of them. If you need make ...

Discover More

Picking a Workbook Format

Need to share workbook information with a wide number of people? It can be puzzling to figure out which version of Excel to ...

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 two more than 4?

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.