hi all,
my problem isn't as simple as it looks from the subject of my post (at
least for me) and its continuation of a previous post.
can someone tell me if its possible to make a cell in excel sheet ready
for editing, by using a macro. that is i want to have the cursor
blinking in the cell so that i can edit the text of the cell by runnig
a particular macro.
my original issue was that i wanted to goto the next line (a line feed)
in the same cell when i press enter key. so i want to change the
defualt behaviour of the enter key, which is to goto next cell. someone
suggested using onkey method but i'm still not able to get cell in edit
mode.
my code.............

Sub testkey()
Application.OnKey "{ENTER}", "LineFeed"
End Sub

Sub LineFeed()
Dim sel As Range
Set sel = Selection
seltxt = sel.Value
withenter = seltxt & vbLf
sel.Value = withenter
End Sub

Hi,
you can do it by using this line but the macro must be called from the
worksheet not from the IDE..

With Application
.SendKeys "{F2}"
.SendKeys "I wrote this"
End With

I wouldn't recommend that approach though...maybe if you posted a bit
more info on what you are trying to achieve?
OJ

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

Macros don't run while you are in edit mode (at least not for any practical
purpose), so you would need to put up a userform and have the user do the
entry in a textbox if you want that kind of control.

--
Regards,
Tom Ogilvy


"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

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

hi Tom,
thanx for reply. do you think that with the onKey method is it possible
to assign a macro, when the enter key is pressed, but then the pressing
of enter key has to be captured while editing the cell and then with
onKey method, a macro may be executed and this macro only feeds a line
in the cell and leaves the cell back in edit mode.

TIA
amit

hi Tom,
thanx for reply. do you think that with the onKey method is it possible
to assign a macro, when the enter key is pressed, but then the pressing
of enter key has to be captured while editing the cell and then with
onKey method, a macro may be executed and this macro only feeds a line
in the cell and leaves the cell back in edit mode.

TIA
amit

No - that is what I said. A macro will not run when you are in Edit mode.
Onkey assignments do not work when you are editing a cell.

--
Regards,
Tom Ogilvy

"helpwithXL" wrote in message
oups.com...
hi Tom,
thanx for reply. do you think that with the onKey method is it possible
to assign a macro, when the enter key is pressed, but then the pressing
of enter key has to be captured while editing the cell and then with
onKey method, a macro may be executed and this macro only feeds a line
in the cell and leaves the cell back in edit mode.

TIA
amit

Troy -
good thought/code - that just might do what he wants.

--
Regards,
Tom Ogilvy


"TroyW" wrote in message
...
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

Tom, thanks.

Troy

"Tom Ogilvy" wrote in message
...
Troy -
good thought/code - that just might do what he wants.

--
Regards,
Tom Ogilvy


"TroyW" wrote in message
...
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