Excel

Move Direction After Hitting Enter

Ease of Use

Easy

Version tested with

2000 

Submitted by:

johnske

Description:

On hitting the Enter key on Sheet1, the next cell selected will be to the right of the active cell. Where-as on hitting the Enter key on Sheet2, the next cell selected will be below the active cell. 

Discussion:

You may have a workbook that has some sheets requiring that data be entered in rows while other sheets may require the data to be entered in columns. By putting the MoveAfterReturn direction as a WorkSheet_Activate event, even workers that are unfamiliar with Excel (and the use of the Up/Down L/R arrows) will be able to enter data quickly in such cases. 

Code:

instructions for use

			

'(Note that there are two other options other than 'the ones given below, they are: xlToLeft and xlUp) '*****CODE FOR SHEET1***** Option Explicit Private Sub Worksheet_Activate() Application.MoveAfterReturnDirection = xlToRight End Sub '************************* '*****CODE FOR SHEET2***** Option Explicit Private Sub Worksheet_Activate() Application.MoveAfterReturnDirection = xlDown End Sub '*************************

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Tools/Project Explorer
  4. Select the Sheet1 module
  5. Copy and paste the code for Sheet1 into this Module
  6. Select the Sheet2 module
  7. Copy and paste the code for Sheet2 into this Module
  8. Now select File/Close and Return To Microsoft Excel
  9. Dont forget to save your changes...
 

Test the code:

  1. Select Sheet1, hit the enter key and note the move direction
  2. Select Sheet2, hit the enter key and note the move direction
  3. (NOTE: If there are other sheets in the workbook without a given Move direction, their move direction will now be the same as the last sheet used that had did have a specified direction)
 

Sample File:

MoveAfterReturn.zip 5.5KB 

Approved by mdmackillop


This entry has been viewed 150 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express