如何在 VBA for Excel 中为动态选择的单元格定义 ENTER 按键事件

How can I define ENTER keypressed event for a dynamically chosen Cell in VBA for Excel

提问by Berker Yüceer

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 ENTERkeypressedat that Cell;

我得到了一个动态选择的单元格，它将填充一些我将要放置的信息，当我放置信息并在该单元格上ENTER按下按键时；

1 - it should trigger a macro

1 - 它应该触发一个宏

'macro(value)
macro1 myinfo

2 - macro should get the info in that cell

2 - 宏应该获取该单元格中的信息

myinfo = Cells( i, j )

So how can i achive that?

那么我怎样才能做到这一点？

回答by Jean-Fran?ois Corbett

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

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

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 myMacromust be run when the Enterkey is pressed. The OnKeymethod only needs to be called once. You could put it in the Workbook_Openevent:

上面说myMacro必须在Enter按下键时运行。该OnKey方法只需要调用一次。你可以把它放在Workbook_Open事件中：

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

To stop capturing the Enterkey,

要停止捕获Enter密钥，

Application.OnKey "{ENTER}"

---

---

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

要检查Enter在单元格 A1 上是否被按下，您可以执行以下操作：

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

---

---

Now to detect if Enterwas 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 OnKeymacro, and after that the Worksheet_Changeevent is triggered. So you first have to "save the results" of OnKey, and then handle the Worksheet_Changeevent based on those results.

现在要检测是否Enter在特定单元格中被按下，仅当该单元格已被编辑时，我们必须有点聪明。假设您编辑了一个单元格值并按 Enter。首先触发的是OnKey宏，然后Worksheet_Change触发事件。因此，您首先必须“保存结果” OnKey，然后Worksheet_Change根据这些结果处理事件。

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

OnKey像这样启动：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_Changeevent:

单元格编辑将被Worksheet_Change事件捕获：

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 Enterkey being pressed? Let us know and maybe we can suggest alternatives.

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

回答by Siddharth Rout

cause ill start a macro when a stock code entered at that cell and give the info of that stock in excel and Worksheet_Change or Change commands will only cause it to get in a loop cause when the stock info being parsed into the cells it will trigger Change event again and again.. – Berker Yüceer 31 mins ago

当在该单元格中输入股票代码并在 Excel 中提供该股票的信息时，导致无法启动宏，Worksheet_Change 或 Change 命令只会导致它进入循环，原因是当股票信息被解析到单元格时，它将触发 Change一次又一次的事件.. – Berker Yüceer 31 分钟前

Berker,

伯克，

For this you don't need to trap the "ENTER" Key. Let's say, you type the Stock Code and instead of pressing ENTER, you clicked on another cell. Wouldn't you like the macro to be fired in that scenario as well? If yes, then try the code below. I am assuming that the macro has to run when the Stock Code is entered in Cell A1.

为此，您不需要捕获“ENTER”键。假设您输入股票代码，而不是按 ENTER，而是单击了另一个单元格。在那种情况下，您不希望宏也被触发吗？如果是，请尝试下面的代码。我假设在单元格 A1 中输入股票代码时必须运行宏。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    '~~> This line ensure that the code will enter into the
    '~~> block only if the change happened in Cell A1
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False

        '
        ' ~~> Put your macro code here or run your macro here
        '
    End If

LetsContinue:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

EDIT: I see you have already selected your answer :)

编辑：我看到你已经选择了你的答案:)

回答by Jilji

use worksheet change event;

使用工作表更改事件；

some thing as below,

一些事情如下，

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A" Then
        ' call your sub

    End If
End Sub

Put this code in the corresponding worksheet module.

将此代码放在相应的工作表模块中。

回答by engmgs

THANKS VERY Much for this and i do i bit change to it as the following:

非常感谢这一点，我对它进行了如下更改：

Dim oldvalue As String Dim newvalue As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa

Dim oldvalue As String Dim newvalue As String Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'~~> This line ensure that the code will enter into the
'~~> block only if the change happened in Cell A1
If Not Intersect(Target, Range("A:D")) Is Nothing Then
    Application.EnableEvents = False

    '
    ' ~~> Put your macro code here or run your macro here
    '
    oldvalue = Range(Target.Address).Value
    Range(Target.Address).Value = Range(Target.Address).Value * 2.33
    newvalue = Range(Target.Address).Value
    MsgBox ("value changed from  " & oldvalue & "  to  " & newvalue)
End If

LetsContinue: With Application .ScreenUpdating = True .EnableEvents = True End With

LetsContinue: With Application .ScreenUpdating = True .EnableEvents = True End With

Exit Sub

Whoa: MsgBox Err.Description Resume LetsContinue End Sub

哇：MsgBox Err.Description Resume LetsContinue End Sub

that will give you a chance to change any cell within range by certain value(i want the cell value to be multiply-ed by factor once the cells value is changed and show me a message that will give old and new value,

这将使您有机会按特定值更改范围内的任何单元格（我希望单元格值在单元格值更改后乘以因子并向我显示一条消息，该消息将提供旧值和新值，

cheers best luck

祝你好运