Line-break in a cell without ALT-ENTER?

K

kenji4861

Is there a way to do this?

I would like to make it so when the user hits "ENTER", it doesn't go to
the next cell. I want it to just make a line-break in the cell.
 
D

Dave Peterson

You could use a couple of macros, but before you do that, how would you really
hit the enter key?

I think I wouldn't really do this, but if had to, I'd remap the enter key from
the numeric keyboard only. Then I could still use the QWERY enter key for
normal usage.

Option Explicit
Sub remapEnterToAltEnter()
Application.OnKey "{enter}", "doAltEnter" 'numeric key pad
'Application.OnKey "~", "doAltEnter" 'QWERTY
End Sub
Sub doAltEnter()
If ActiveCell.HasFormula Then
'do nothing
Else
ActiveCell.Value = ActiveCell.Value & Chr(10)
SendKeys "{f2}{end}{right}"
End If
End Sub
Sub ResetEnterKeys()
Application.OnKey "{enter}" 'numeric
'Application.OnKey "~" 'QWERTY
End Sub

I commented out the QWERTY lines if you decide to use them. (on the other
hand, isn't it easier to just hit alt-enter than to remember which enter key
does what? <vbg>.)
 
K

kenji4861

Hi Dave and thanks.

I'm a bit of a newbie to this.. but do I enter this.. after pressing
ALT-F11?

Where do I enter this.. in the Sheet I'm working on? Thanks!
 
D

Dave Peterson

Edited from a private message:

Put the code in a general module.

You can turn it on via tools|macro|macros and running the one that redefines the
key. (This is named: remapEnterToAltEnter)

And you can toggle it off by running the reset version.
(This is named: ResetEnterKeys)

If you want it to be automatic, rename the one that changes the key (not the one
that actually does the work) to Auto_open.
(rename: remapEnterToAltEnter to Auto_Open)

And rename the one that resets the keyboard Auto_Close.
(rename: ResetEnterKeys to auto_close)

Then whenever you open that workbook, you'll be set. And when you close that
workbook, the keys go back to normal.
 
K

kenji4861

Nice! I see, I need to run that macro once for this to work. AWESOME!
THANKS DAVE!
 
Top