更改单元格时，VBA运行宏，但如果是宏，则运行宏

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 B4 a 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 B4 to change (as B4 references 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.

希望这个问题有道理。

3 个解决方案

#1

You can disable the Worksheet_Calculate Events by using Application.EnableEvents as below. Please note this will disable any WorkSheet or WorkBook event that may occur in-between Application.EnableEvents = False and Application.EnableEvents = True

您可以使用Application.EnableEvents禁用Worksheet_Calculate事件，如下所示。请注意，这将禁用Application.EnableEvents = False和Application.EnableEvents = True之间可能发生的任何WorkSheet或WorkBook事件。

So if your other sub was run like this - the Worksheet_Calculate event won't fire

因此，如果您的其他子类运行如此 - Worksheet_Calculate事件将不会触发

Sub Other_Sub()
Application.EnableEvents = False
[b4].Value = "10"
'other stuff
Application.EnableEvents = True
End Sub

#2

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”。

#3

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)

并注意（退出子）或（退出函数）...不要忘记在（退出子）或（退出函数）命令之前使用Application.EnableEvents = True（如果存在）

#1