如何使用vba在Excel中的ENTER键上调用函数 [英] How to call a function on ENTER key in Excel using vba

问题描述

Google应该为我提供充足的例子，但是他们似乎都没有工作

我想要什么：每次用户按下，然后释放，ENTER键，为我的程序做一些事情（即创建一个MsgBox或调用函数Foo）。我更喜欢这样一种MWE的形式。

我所做的：我尝试过没有一个例子是功能的。他们编译，但不做任何事情。我还确保保存在宏兼容的Excel格式。

我使用的是什么：我正在使用Excel 2016，64位Office 365

编辑：用户正在将该信息输入工作表。我想拦截用户输入，每次按ENTER键，将光标/活动单元格向下移动两行，因此每个单元格下面都有一个空单元格。如果用户按Tab键，我想将光标/活动单元格右移两列，因此每个单元格右侧都有一个空单元格。

编辑2：这里是一个MWE，我现在有什么应该工作，但什么都不做。我将这个添加到工作表中，而不是作为一个模块。

  Sub SomeActions（）
 MsgBox（Hello ）
 End Sub 
 
 Private Sub Workbook_Open（）
 Application.OnKey〜，SomeActions
 End Sub 
  

解决方案

首先，进行回调 Sub 你需要的逻辑将它放入一个新的代码模块（不在工作表代码中）：

  Sub SomeActions（）
 ... 
 End Sub 
  

然后，订阅 OnKey 事件，例如，当用户在VBA编辑器中打开工作簿（此代码进入 ThisWorkbook ）模块时：

  Private Sub Workbook_Open（）
 Application.OnKey〜，SomeActions
 End Sub 
  pre> 
 
 
 〜表示输入键。对于数字键盘键使用{ENTER}。
 
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

解决方案
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}".

                        
这篇关于如何使用vba在Excel中的ENTER键上调用函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！