如果触发另一个更改事件，则 VBA 可防止触发更改事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9905583/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to prevent a change event from triggering if another change event triggers
提问by Bruder
this one may be impossible to solve in VBA but I'd like to see what you experts have to say about it.
这个问题在 VBA 中可能无法解决，但我想看看各位专家对此有何看法。
I have a textbox on a userform that triggers a macro within a TextBox1_Change() type of sub.
我在用户窗体上有一个文本框，它在 TextBox1_Change() 类型的 sub 中触发宏。
If the user types "ABC" in the textbox, the macro gets triggered 3 times: once for "A", once for "AB" and once for "ABC". This macro is actually kind of heavy, so I would like it to run only when the user is actually done typing, and not inbetween single key strokes.
如果用户在文本框中键入“ABC”，则宏被触发 3 次：一次是“A”，一次是“AB”，一次是“ABC”。这个宏实际上有点重，所以我希望它只在用户实际完成输入时运行，而不是在单次击键之间运行。
I know I can make the user "press enter" or whatever and only then run the macro, but this is not what I'm looking for. I want him to type freely and see the results of his typing dynamically show up, with no other type of interaction required.
我知道我可以让用户“按 Enter”或其他什么，然后才运行宏，但这不是我要找的。我希望他可以自由地打字并看到他的打字结果动态显示，不需要其他类型的交互。
So, I came up with the idea of making the change event wait and see if another change event gets triggered within, say, 1 second from the first. If that happens, the first change event aborts.
所以，我想出了让更改事件等待并查看是否在第一个更改事件的 1 秒内触发另一个更改事件的想法。如果发生这种情况，第一个更改事件将中止。
Now this would work, and I think I would know how to code it, except that I don't know how to give the user the power to keep typing even when the first change event is running.
现在这会起作用，我想我会知道如何编码它，只是我不知道如何让用户即使在第一个更改事件正在运行时也能继续输入。
What I mean is that when the first macro runs, it "freezes" everything. Waiting to see if another change event triggers will therefore not work, as nothing is going to trigger until the first macro is done running.
我的意思是，当第一个宏运行时，它会“冻结”所有内容。因此，等待查看是否触发另一个更改事件将不起作用，因为在第一个宏运行完成之前不会触发任何内容。
Do you guys see my problem here? How would you go about this? Any chance I can achieve the results I'd like?
你们看到我的问题了吗？你会怎么做？我有机会达到我想要的结果吗？
Any help is greatly appreciated, thanks guys!
非常感谢任何帮助，谢谢大家！
回答by Jean-Fran?ois Corbett
I tested the following, and it works (assuming I correctly understand what you're trying to do).
我测试了以下内容，并且它有效（假设我正确理解您要做什么）。
In a code module, write this:
在代码模块中，这样写：
Public aRunIsScheduled As Boolean
Public nextRunTime As Variant
Sub MyMacro()
'Flag macro as having been run, no longer scheduled.
aRunIsScheduled = False
'Place your macro code here.
'I'll just use some dummy code:
MsgBox "MyMacro is running!"
End Sub
In your sheet module:
在您的工作表模块中：
Private Sub CommandButton1_Click()
If aRunIsScheduled Then
' Cancel the previously scheduled run.
Application.OnTime EarliestTime:=nextRunTime, _
Procedure:="MyMacro", Schedule:=False
aRunIsScheduled = False
End If
' Schedule a new run 3 seconds from now:
nextRunTime = Now + TimeValue("00:00:03")
Application.OnTime EarliestTime:=nextRunTime, _
Procedure:="MyMacro", Schedule:=True
aRunIsScheduled = True
End Sub
I put a Commandbutton in my sheet and here I'm using its change event, but you can put this code in your TextBox1_Change()event instead, in exactly the same way.
我在我的工作表中放置了一个命令按钮，在这里我使用了它的更改事件，但是您可以TextBox1_Change()以完全相同的方式将此代码放入您的事件中。
