vba VBA在单元格更改时运行宏，但如果是宏则不运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13267850/
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 run a macro when cell is changed, but not if by a macro
提问by harryg
OK I'm not sure if this is easily achievable but I'm going to try.
好的，我不确定这是否容易实现，但我会尝试。
I use this sub to execute some macros if a cell is changed:
如果单元格被更改，我使用这个子程序来执行一些宏：
Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("b4")
If Not Intersect(target, Range("b4")) Is Nothing Then
Call init
End If
End Sub
This works fine but I have a bit of a problem.
这工作正常，但我有一点问题。
The cell B4, as referenced in the cell change sub above, has its value determined by a named range which is dynamic and contains a list of values on another sheet. I use the data validation tool to make B4a dropdown list with the contents of the named range.
细胞B4，如在小区变更子引用的上方，具有由这是动态的，并包含在另一片的值的列表中的命名范围确定其值。我使用数据验证工具制作B4一个包含命名范围内容的下拉列表。
I have another macro who's purpose is to update this list. What it does is clear the current list, query a database and output a bunch of values into the range. The trouble is that when this macro is run it causes the value of B4to change (as B4references the values in the range). This in turn cause my "cell change" macro to run throwing up errors.
我有另一个宏，其目的是更新此列表。它的作用是清除当前列表，查询数据库并将一堆值输出到范围内。麻烦的是，当这个宏运行时，它会导致 的值发生B4变化（作为B4引用范围内的值）。这反过来会导致我的“单元格更改”宏运行引发错误。
Is there a way to prevent the "cell change" macro from running while I'm updating the list that it references?
当我更新它引用的列表时，有没有办法防止“单元格更改”宏运行？
Hope that question makes sense.
希望这个问题有意义。
回答by brettdj
You can disable the Worksheet_CalculateEvents by using Application.EnableEventsas below. Please note this will disable any WorkSheetor WorkBookevent that may occur in-between Application.EnableEvents = Falseand Application.EnableEvents = True
您可以Worksheet_Calculate使用Application.EnableEvents以下方法禁用事件。请注意，这将禁用任何WorkSheet或WorkBook事件可能发生在中间Application.EnableEvents = False和Application.EnableEvents = True
So if your other sub was run like this - the Worksheet_Calculateevent won't fire
因此，如果您的其他潜艇是这样运行的 -Worksheet_Calculate事件将不会触发
Sub Other_Sub()
Application.EnableEvents = False
[b4].Value = "10"
'other stuff
Application.EnableEvents = True
End Sub
回答by harryg
Never mind, I have worked out a simple solution: Just put a conditional statement in saying to not execute "init" if B4 contains an error or is blank.
没关系，我已经找到了一个简单的解决方案：只要在 B4 包含错误或为空白的情况下放置一个条件语句，就不要执行“init”。
回答by Mahdi Jazini
and be aware of (exit sub) or (exit function)... Don't forget to use Application.EnableEvents = True before (exit sub) or (exit function) commands (if exists)
并注意 (exit sub) 或 (exit function)... 不要忘记在 (exit sub) 或 (exit function) 命令（如果存在）之前使用 Application.EnableEvents = True
