如何为VBA for Excel中动态选择的单元格定义ENTER键压缩事件 [英] How can I define ENTER keypressed event for a dynamically chosen Cell in VBA for Excel

问题描述

我有一个动态选择的单元格，将填充一些信息，即将放置，当我把信息和 ENTER keypressed 在该单元格;

1 - 它应该触发宏

 '宏（值）
宏1 myinfo 
  

2 - 宏应该得到该单元格中的信息

  myinfo = Cells（i，j）
  

那么我该怎么办？

解决方案

要捕获按下的特定键，您需要 OnKey 方法：

  Application.OnKey〜，myMacro'用于常规输入键
'，或者如果要从数字键盘输入：
'Application.OnKey{ENTER}，myMacro 
'下面我会假设你想要后者。 
  

上面说的是要运行 myMacro 当 Enter 键被按下时。 OnKey 方法只需要调用一次。您可以将它放在 Workbook_Open 事件中：

  Private Sub Workbook_Open ）
 Application.OnKey{ENTER}，myMacro
 End Sub 
  

要停止捕获输入键，

  Application.OnKey{ENTER}
  

---

要检查 Enter 是否在单元格A1上按下，可以这样做：

  Sub myMacro（）
如果不相交（选择，范围（A1））Not Not Then然后
'相当于但比b $ b更灵活和更健壮如果Selection.Address =$ A $ 1然后
 MsgBox A1。 
如果
 End Sub 
  

---

现在检测输入是否在特定单元格中被按下，只有当该单元格被编辑时，我们必须有点聪明。假设您编辑单元格值，然后按Enter键。触发的第一件事是 OnKey 宏，之后触发 Worksheet_Change 事件。所以你首先必须保存结果 OnKey ，然后根据这些结果处理 Worksheet_Change 事件。

启动 OnKey 如下： Application.OnKey{ENTER}，recordEnterKeypress

在您的代码模块中，您可以：

  public enterWasPressed As Boolean 
 
 Sub recordEnterKeypress（）
 enterWasPressed = True 
 End Sub 
  

细胞编辑将由 Worksheet_Change 事件捕获：

如果enterWasPressed _
并且不相交（目标，范围（A1））没有任何
$ b MsgBox你刚刚修改了单元格A1并按下Enter。
End If
enterWasPressed = False'reset it
End Sub

---

现在，上面的代码做了你在问题中提出的问题，但我想重申一下：你的问题听起来很像一个 XY问题。为什么要检测被按下的 Enter 键？让我们知道，也许我们可以提出替代方案。

I got a dynamically chosen Cell that will be filled with some information that im going to put and when I put the information and ENTER keypressed at that Cell;

1 - it should trigger a macro

'macro(value)
macro1 myinfo

2 - macro should get the info in that cell

myinfo = Cells( i, j )

So how can i achive that?

解决方案

To capture a specific key being pressed, you need the OnKey method:

Application.OnKey "~", "myMacro" ' for the regular enter key
' or if you want Enter from the numeric keypad:
' Application.OnKey "{ENTER}", "myMacro"
' Below I'll just assume you want the latter.

The above says that myMacro must be run when the Enter key is pressed. The OnKey method only needs to be called once. You could put it in the Workbook_Open event:

Private Sub Workbook_Open()
    Application.OnKey "{ENTER}", "myMacro"
End Sub

To stop capturing the Enter key,

Application.OnKey "{ENTER}"

---

To check whether Enter was pressed while on cell A1, you could do this:

Sub myMacro()
    If Not Intersect(Selection, Range("A1")) Is Nothing Then
    ' equivalent to but more flexible and robust than
    'If Selection.Address = "$A$1" Then
        MsgBox "You pressed Enter while on cell A1."
    End If
End Sub

---

Now to detect if Enter was pressed in a specific cell only if that cell has been edited, we have to be a bit clever. Let's say you edit a cell value and press Enter. The first thing that is triggered is the OnKey macro, and after that the Worksheet_Change event is triggered. So you first have to "save the results" of OnKey, and then handle the Worksheet_Change event based on those results.

Initiate OnKey like this: Application.OnKey "{ENTER}", "recordEnterKeypress"

In your code module you would have this:

Public enterWasPressed As Boolean

Sub recordEnterKeypress()
    enterWasPressed = True
End Sub

The cell edit will be captured by the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If enterWasPressed _
        And Not Intersect(Target, Range("A1")) Is Nothing Then
        MsgBox "You just modified cell A1 and pressed Enter."
    End If
    enterWasPressed = False 'reset it
End Sub

---

Now, the above code does what you ask in the question, but I would like to reiterate: your question sounds awfully like an XY problem. Why do you want to detect the Enter key being pressed? Let us know and maybe we can suggest alternatives.

这篇关于如何为VBA for Excel中动态选择的单元格定义ENTER键压缩事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！