MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using the Carriage Return

Posted by Jamie on July 01, 2000 4:18 PM

Hi all,
I'm trying to edit within a cell, (using excel 97) and I need to use the carriage return to specify a new line in the same cell. I'm aware of how to use ALT+ENTER and that works but, I've got several of these cells to do and I was wondering if there was a way, using VBA or otherwise, to have the enter key work the same way as the ALT+ENTER key. Any and all help will be appreciated.

Posted by Ivan Moala on July 03, 0100 3:56 AM

You can try something like this;

In your Thisworkbook object

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'You can then use the ENTER key from
'The Number pad to advance the activecell
'to the next cell
Application.OnKey "~", ""
End Sub

Private Sub Workbook_Open()
Application.OnKey "~", "Alt_Enter_Key"
End Sub

In a Module put this code;

Sub Alt_Enter_Key()
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & Chr(10)
Application.SendKeys ("{F2}"), False
End Sub

Now when you want to enter data in a cell
USE the ENTER key to simulate ALT ENTER
When you have finished entering the data
USE ENTER key on your number Pad.