VBA Excel - Replace Enter key with Alt Enter-CodePudding

I am trying to replace Enter key with Alt Enter so that I can write multiline in cells with ease.

I have seen that there is a function Application.OnKey and Application.SendKeys and I wanted to use those something like this:

Application.OnKey "~" , Application.SendKeys("%~")

But where do I place those? Or any other ideas?

CodePudding user response：

I think I agree with @Andreas, this is unlikely to work using these methods.

This is what I tried: I made a button Button1 and in its click method I assign the Enter key to send Alt-Enter as you suggest in the question:

Sub Button1_onClick()

    Call Application.OnKey("~", "SendAltEnter")

End Sub

Sub SendAltEnter()

    Application.SendKeys ("%~")
    
End Sub

This does in fact re-route the Enter key, but apparently the Alt-Enter results in another call to the method for the "Enter" part of "Alt-Enter" -- it results in an infinite loop the first time you hit enter after having clicked the button, and you have to restart your Excel application to clean it up.

I also tried this, simply using another key near Enter, namely # (at least on German keyboards) which you could hit instead of Alt-Enter:

Sub Button1_onClick()

    Call Application.OnKey("#", "SendAltEnter")

End Sub

Sub SendAltEnter()

    Application.SendKeys ("%~")
    
End Sub

The key '#' is intercepted, but not if you are in input mode in a cell, only if the focus is somewhere in the worksheet.

I think you'll have to do this outside of Excel using a keyboard remapping tool for Windows. I quickly found https://www.howtogeek.com/710290/how-to-remap-any-key-or-shortcut-on-windows-10/ by googling but know nothing about it or if it is legit or not.

CodePudding user response：

Have you considered just using Shift Enter to insert a carriage return instead?