VBA to simulate ALT {ENTER} in cell

[RobbieC]

Hi there, I have a bit of code to make the ENTER key add a carriage return whilst entering text into cell:

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

but this will only put a new line at the end of the existing content in a cell.

Is there a way to do this but to simulate ALT + {ENTER} ?.. So that it inserts the linebreak at the point of the cursor and not at the end. I've looked everywhere, but I can't find anything...

If you can point me in the right direction, I'd be very grateful

Thanks

 

[RoryA]

You can't run code in edit mode in a cell, so I'm afraid not. Why can't you just use Alt+Enter?

 

[RobbieC]

But my code above using sendkeys on the RETURN key does work whilst in edit mode on a cell. It is running the sub carriage_return... which puts a new line at the end of the current text

If it could be replaced with something along the lines of SendKeys "%{ENTER}" (ALT ENTER)

or the code could sit on a button with a macro to insert the linebreak at the point of the cursor

 

[RoryA]

But my code above using sendkeys on the RETURN key does work whilst in edit mode on a cell.

It doesn't actually. It exits edit mode and then runs the code, so the cursor isn't anywhere in the formula bar at the point the code runs.

 

[RobbieC]

ah, I see. Thanks Rory.

I shall put a note on the document for the users to use ALT + ENTER to add a new line

 

[dfsobral]

depending on your users it may be helpfull to use an input box where users will insert the new text

then you add it through the code to the cell