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:
Figure 1. The Advanced options of the Excel Options dialog box.
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:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13073) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Referring to cells is typically done using a letter and a number, which represent the column and row. That's not the only ...
Discover MoreWhen you freeze panes in a worksheet, those panes should persist even though you save the workbook and reload it. There ...
Discover MoreWhen you create a shared workbook, you run the risk of losing some of the data in that workbook. Here's a discussion ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-03-26 06:04:42
Alberto
Hi Allen, maybe off topic but I have seen that many people have this problem and found no solution whatsoever online.
I am working on a workbook that suddently makes me hit enter twice in order to move from the cell. It's not happening in other workbooks and it didn't happen before on this one.
Do you have any idea what can be the cause and the solution?
Manu thanks
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
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
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
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?
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Comments