Personally, I would not recommend changing such a basic user interface
element as the "Enter" key. It's not clear to me how you are going to
control when the "Enter" key trap is enabled and disabled. Failure to
control this properly could lead to an unhappy and confused user.

Another question is how will the user complete the cell entry? If the
"Enter" key is being trapped then it can't be used. The user will need to
remember to use one of the arrow keys, page up/down, ctrl-enter, click
another cell with the mouse, or use the numeric keypad "Enter" key.

But, give the code below a try. It is a workaround of the limitations that
Excel places on running macros while in Edit mode. The way the
Application.OnKey method appears to behave is that it completes the cell
entry and then it proceeds with the key trap code. Hence, the code below is
only able to append a linefeed to the end of the in-progress cell entry. It
gives some of the feel that you are requesting, but it is not truly trapping
the "Enter" key in the fashion you originally requested.

Please note that the Application.OnKey method makes a distinction between
the "Enter" key on numeric keypad (represented as {ENTER}) and the "Enter"
key located above the "Shift" key (represented as ~). This code traps the ~
(tilde) version of the "Enter" key. Good luck.

Troy


Sub Trap_Enter_On()
Application.OnKey "~", "Trap_EnterKey"
End Sub

Sub Trap_Enter_Off()
Application.OnKey "~", ""
End Sub

Sub Trap_EnterKey()
If TypeName(Selection) = "Range" Then
If Len(ActiveCell.Formula) 0 Then
If Left$(ActiveCell.Formula, 1) < "=" Then
ActiveCell.Formula = ActiveCell.Formula & vbLf
Application.SendKeys "{F2}", True
End If
End If
End If
End Sub


"helpwithXL" wrote in message
oups.com...
hi OJ,
my problem is that while editing the cell, i want to go to the next
line in the SAME cell when i press enter and not to the next cell (as
the default action of pressing enter key). i can go to next line in
same cell by alt+enter (default again) but i have a specific
requirement in which i have to over ride enter key so that when user
presses enter key , the cursor remains in the same cell but on the next
line.

TIA
regards