我得到了一个动态选择的Cell,它将填充我要放入的一些信息,当我放入信息时,ENTER在该Cell处按键;
1-它应该触发宏
'macro(value)
macro1 myinfo
2-宏应该获取该单元格中的信息
myinfo = Cells( i, j )
那么我该如何实现呢?
要捕获被按下的特定键,您需要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.
上面说按下Enter键时必须运行myMacro
。OnKey
方法只需要调用一次。你可以把它放在Workbook_Open
事件中:
Private Sub Workbook_Open()
Application.OnKey "{ENTER}", "myMacro"
End Sub
要停止捕获输入键,
Application.OnKey "{ENTER}"
要检查在单元格A1上是否按下Enter,可以执行以下操作:
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
现在,要检测特定单元格中是否按下了Enter,只有当该单元格已被编辑时,我们必须稍微聪明一点。假设您编辑了一个单元格值,然后按Enter键。首先触发的是OnKey
宏,然后触发Worksheet_Change
事件。因此,您首先必须"保存OnKey
的结果",然后根据这些结果处理Worksheet_Change
事件。
像这样启动OnKey
:Application.OnKey "{ENTER}", "recordEnterKeypress"
在你的代码模块中,你会有这样的:
Public enterWasPressed As Boolean
Sub recordEnterKeypress()
enterWasPressed = True
End Sub
单元格编辑将被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
现在,上面的代码完成了您在问题中提出的内容,但我想重申:您的问题听起来非常像XY问题。为什么要检测按下Enter键?让我们知道,也许我们可以提出替代方案。
当在该单元格中输入股票代码并在excel中提供该股票的信息时,会导致启动宏,Worksheet_Change或Change命令只会导致它进入循环,因为当股票信息被解析到单元格中时,它会一次又一次地触发Change事件Berker Yüceer 31分钟前
Berker,
为此,您不需要对"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
编辑:我看你已经选择了答案:)
使用工作表更改事件;
下面是
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
' call your sub
End If
End Sub
将此代码放入相应的工作表模块中。
非常感谢,我对它做了如下更改:
将旧值标注为字符串将新值标注为字符串专用子工作表_更改(按价值目标作为范围)关于错误GoTo-Wowa
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
Lets继续:带应用程序.Screen Updating=True.EnableEvents=True以结束
Exit Sub
哇:MsgBox错误。描述简历Lets继续结束子
这将使您有机会将范围内的任何单元格更改为某个值(我希望单元格值更改后将单元格值乘以因子,并向我显示一条消息,给出新旧值,
欢呼祝好运