Excel VBA how do I make the Enter key behave like the Tab key

I'd like the enter key to function just like the tab key does when using the worksheet to enter values. I'm not sure if that's enough information for you to help me so just ask questions if it's not.

I 开发者_JAVA技巧was able to find a setting in Tools>Options>Edit Move selection after enter - right. I'd like that same functionality but only for the one specific worksheet.

use the Application.MoveAfterReturnDirection property - best to set in a Workbook_Open().

e.g.

Application.MoveAfterReturn = True  ' here we say thou shallst move
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right

Hope that helps Good luck MikeD

edit:

if you have more than 1 book open in the same application, you better use Workbook and Sheet Activate and Deactivate triggers as well to limit the effect to the narrowest possible

edit 2:

you can maybe save the original status in a trigger in the ThisWorkbook module

Dim OldDirection As Long

Private Sub Workbook_Open()
    OldDirection = Application.MoveAfterReturnDirection
End Sub

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

and rely on Sheet_Activate and Sheet_Deactivate to actually set the property to xlToRight and reset it to OldDirection (so whatever the user chose as standard behaviour). Test with multiple open workbooks, jump back and forth; these triggers not always do the obvious at obvious times ....