Different Cell Movement in a Single Worksheet

Written by Allen Wyatt (last updated November 9, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


1

Patrick wonders if there is a way to have the cell movement move to the right on one worksheet in a workbook and down on another worksheet in the same workbook. The only thing he can find is a global setting for all the worksheets in the workbook.

Patrick is correct; this setting is something that is handled globally in Excel. You can change the setting 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 or a later version, 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 "After Pressing Enter, Move Selection" check box is checked. (It should be checked, by default.)
  5. Using the Direction drop-down list, change the direction as desired.
  6. Click OK.

This changes the direction of movement for all workbooks you open in Excel. If you want the movement to vary for a single worksheet, you can accomplish this through the use of a few event handlers. Two of these are attached to the worksheet you want to be handled differently; just right-click on the worksheet's tab and select Code from the resulting Context menu. You can then paste the following into the Code window:

Private Sub Worksheet_Activate()
    Application.MoveAfterReturnDirection = xlToRight
End Sub
Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

These event handlers are triggered, respectively, when the worksheet is activated and when you deactivate the worksheet by selecting a different worksheet. Note that upon activation, the direction is set to xlToRight, which means that movement will by to the right. When the worksheet is deactivated, the direction is set to xlDown, which is assumed to be the default direction.

You will also need a third event handler; this one should be added to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.MoveAfterReturnDirection = xlDown
End Sub

This is necessary because if you close Excel while the "move right" worksheet is active, then the movement direction remains set to xlToRight. This means that the next time you start Excel, that becomes the default direction. By including the BeforeClose event handler, you ensure that the default is set as it should be.

There is only one small potential gotcha with this approach: When you first open a workbook, the Activate event is not triggered for whatever worksheet is first displayed. This means that if your "move right" worksheet is displayed for the workbook, by default, the movement won't be changed to xlToRight; it will still be set to the default, which is presumably xlDown. To force the event handler to run, you'll need to display a different worksheet in the workbook and then come back to the original worksheet. If you would rather not do this manually, you could add a fourth event handler, this one also in the ThisWorkbook module:

Private Sub Workbook_Open()
    Sheets("Sheet2").Select
    Sheets("Sheet1").Select
End Sub

This macro selects the worksheet named Sheet2 and then immediately selects the one named Sheet1. You should, of course, change the worksheet names to reflect the ones appropriate for your workbook—just make sure that the last worksheet selected is the "move right" worksheet.

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 (13640) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.

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

Spacing Table Rows Vertically

Want to get just the right amount of spacing above and below text in a table cell? A very easy way to do this is to ...

Discover More

Using the FORECAST Function

Excel provides a handy worksheet function that allows you to forecast values based upon a set of known values. This ...

Discover More

Generating a Gift Exchange List

Want to figure out how to do a gift exchange for your family or office? There are a variety of ways you can approach the ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!

More ExcelTips (ribbon)

Disabling Page Layout View

Excel allows you to display your workbooks using a couple of different views. If you want to disable one of the views, it ...

Discover More

Fixing the Decimal Point

Don't want to always type the decimal point as you enter information in a worksheet? If you are entering information that ...

Discover More

Changing Excel's Starting Date

When keeping track of dates, internally, Excel uses special date serial numbers that are based upon a specific day in the ...

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}] (all 7 characters, in the sequence shown) 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 seven more than 2?

2024-11-09 10:07:42

J. Woolley

For more on this subject, see my comment here:
https://excelribbon.tips.net/T007220#comment-form-hd


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.