Make the ENTER key act like the TAB key.

[LordDragon]

Greetings,


I am trying to make the ENTER key act like the TAB key. However, I don't just want it to move the focus to the right, I want it to jump to the next unprotected cell, just like pressing TAB does.


I have tried all the following codes, and they all work as far as they move the focus right, but they don't jump the focus to the next unprotected cell.


This one is supposed to be placed in the ThisWorkbook module:


Private Sub Workbook_Activate()


Application.MoveAfterReturnDirection = xlToRight


End Sub


Private Sub Workbook_Deactivate()


Application.MoveAfterReturnDirection = xlDown


End Sub



This one can be put in any module:


Public Sub Enter_KeyPress(KeyAscii As Integer)


If KeyAscii = 13 Then 'The ENTER key.
SendKeys "{tab}" 'Set the focus to the next control.
KeyAscii = 0 'Ignore this key.
End If

End Sub



This one can also be put in any module:


Sub EnterKeyBehavior()


If Application.MoveAfterReturnDirection = xlToRight Then
Application.MoveAfterReturnDirection = xlDown
Else: Application.MoveAfterReturnDirection = xlToRight
End If


End Sub



Personally, I like just pressing TAB, however, there are several people who don't naturally do that and to use my workbook it is much easier to TAB through than to click and type.

[Paul_Hossler]

Another way might be to remap the 2 enter keys when you open the WB and unmap then when you deactivate or close the WB

Personally, I like the Tab key also

On my PC with my KB, this code flashes the NumLock. Don't know if it's my KB or the code so try it out

The attachment is my little test. The WS is protected, and all cells are locked except for A1 and the UNLOCKED cells. Run the map macro, start in A1 and 'enter' your way across to see if this helps



Option Explicit

Sub RemapEnterKey()
'numeric keypad
Call Application.OnKey("{ENTER}", "NewEnter")
'regular Enter
Call Application.OnKey("~", "NewEnter")
End Sub


Sub UnmapEnterKey()
'numeric keypad
Call Application.OnKey("{ENTER}")
'regular Enter
Call Application.OnKey("~")
End Sub

Sub NewEnter()
If Not TypeOf Selection Is Range Then Exit Sub
Application.SendKeys "{TAB}"
End Sub

[Aflatoon]

On my PC with my KB, this code flashes the NumLock. Don't know if it's my KB or the code so try it out

It's a side effect of using SendKeys.

[Kenneth Hobs]

I don't have that issue using a wireless keyboard but like Aflatoon said, it can be an issue with that method.

You would think that that feature would be a default or at least an option one could pick. I used to force that issue and then some when I programmed in mainframe SAS many years ago.

If you care to test, you can try an API method. I attached a file based on Paul's example where I added a class for sending keys by API methods, modified Paul's routine to use the class, and added routines to run his routines in ThisWorkbook object.

[Paul_Hossler]

Nice - that gets rid of the 'Num Lock' flicker

[LordDragon]

Ken & Paul,

Thanks, this worked perfectly.

I used the version Ken modified that included the class module. I too use a wireless keyboard, but not all the users will.