VBA - disable {ENTER} key but enable carriage return in cell...

[RobbieC]

Hi there, I have a workbook which is purely for user input ie. the user is just inputting data.

Many of the cells are designed to hold large amounts of text (which are formatted to wrap text). However when many people type, they like to hit the {return} key to invoke a carriage return. Excel always goes to the next cell below when {return} is hit...

Is there a way to invoke a carriage return in a cell rather than go to the next cell?

If you can point me in the right direction, I'd be most grateful. Thanks

 

[Jaafar Tribak]

Maybe something like this :

Sub HookEnterKey()
    Application.OnKey "{RETURN}", "carriage_return"
    Application.OnKey "{ENTER}", "carriage_return"
End Sub

Sub carriage_return()
    ActiveCell = ActiveCell & Chr(10)
    SendKeys "{F2}"
    DoEvents
    SendKeys "{F2}"
End Sub

Do not forget to unhook the keys when not needed.

 

[RobbieC]

This is almost working Jaafar, when you hit {enter} the carriage returns works, but the cursor stops flashing in the cell, therefore when you type something else (a second line) it just writes over what has already been inputted...

How can I keep the cursor i the cell after the carriage return?

 

[Jaafar Tribak]

The code I posted worked for me fine and I didn't experience the problem you described.
Try removing the the DoEvents statement and the second call to SendKeys from the carriage_return sub and see what you get .

 

[RobbieC]

Brilliant Jaafar. I saved the workbook, closed it and re-opened it and it worked! Not sure why it wasn't working before...

Thanks very much for your help