How to call a function on ENTER key in Excel using vba

Question

Google should provide me with ample examples but none of them seem to work

What I want: Everytime the user presses, and then releases, the ENTER key, for my program to do do something (ie. create a MsgBox, or call function Foo). I would prefer this in the form of a MWE

What I have done: I have tried googling it but none of the examples are functional. They compile, but don't do anything. I have also made sure to save in a macro compatible Excel format.

What I am using: I am using Excel 2016, 64 bit with Office 365

EDIT: The user is entering this information into the worksheet. I want to intercept the user input and everytime they press ENTER, take the cursor/active cell down two rows, so there is an empty cell below every cell. If the user presses tab, I want to take the cursor/active cell right two columns, so there si an empty cell to the right of every cell.

EDIT 2: here is a MWE of what I have right now which should work, but which does nothing. I am adding this to the worksheet, and not as a module

Sub SomeActions()
    MsgBox ("Hello")
End Sub

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

Press Enter where? Worksheet? Userform? A little more details will help :) — Siddharth Rout, Jul 27 '16 at 15:57
Does this help? http://stackoverflow.com/questions/11153995/is-there-any-event-that-fires-when-keys-are-pressed-when-editing-a-cell — Alexis Olson, Jul 27 '16 at 15:59
Or [this](http://stackoverflow.com/questions/21453681/can-i-capture-and-emulate-a-keydown-event-in-excel-vba) ... an old @SiddharthRout answer. — Scott Holtzman, Jul 27 '16 at 16:01
@AlexisOlson I saw that, but it is 32 bit and causes a compilation error. Simply changing the 32's to 64's does not fix the problem — puk, Jul 27 '16 at 16:03
@ScottHoltzman I copied it over into the workbook and module locations respectively, but it is complaining about the user32. How do I get around that? — puk, Jul 27 '16 at 16:06

ttaaoossuuuu · Accepted Answer · 2016-07-27T16:17:30.243

2

First, make an callback Sub that performs the logic you need. Put it into a new code module (NOT into worksheet code):

Sub SomeActions()
...
End Sub

Then, subscribe to OnKey event, for example, when the user opens the workbook (this code goes into ThisWorkbook) module in VBA editor:

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

"~" means Enter key. For numeric keypad key use "{ENTER}".

edited Jul 27 '16 at 16:17

answered Jul 27 '16 at 16:06

ttaaoossuuuu

7,786
3
28
58

I tried that but it doesn't work. I will copy paste exactly what I have into my question. – puk Jul 27 '16 at 16:09
OK some progress...when I put it in `ThisWorkbook`, it generates a run time error which reads "cannot run the macro ... the macro may not be available in this workbook or all macros may be disabled" – puk Jul 27 '16 at 16:14
Check if the Sub you subscribe to is actually the one (i.e. `SomeActions`). Also it must be in a module, not in worksheet code (see edit). – ttaaoossuuuu Jul 27 '16 at 16:17

score 2 · Answer 2 · answered Jul 27 '16 at 16:22

^{What I want: Everytime the user presses, and then releases, the ENTER key, for my program to do do something (ie. create a MsgBox, or call function Foo).}
_{I would prefer this in the form of a MWE}

If I read this correctly, you want a blank row between every user inputted value doing down and a blank column between every user inputted value going right.

On the worksheet code sheet:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Dim r As Long, c As Long
    If Target.Row / 2 <> Int(Target.Row / 2) Then r = 1
    If Target.Column / 2 <> Int(Target.Column / 2) Then c = 1
    Target.Offset(r, c).Activate
    Application.EnableEvents = True
End Sub

This occurs for all navigation. If you only want this to occur on input, modify to suit a Worksheet_Change event macro instead of a Worksheet_SelectionChange event macro.

How to call a function on ENTER key in Excel using vba

2 Answers2

Linked

Related