VBA在更改单元格时运行宏，但在更改宏时不运行 [英] VBA run a macro when cell is changed, but not if by a macro

问题描述

好的，我不确定这是否容易实现，但我将尝试.

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.

如上面的单元格更改子句中所引用的那样，单元格B4的值由命名范围确定，该范围是动态的，并在另一张纸上包含值列表.我使用数据验证工具将B4设置为包含命名范围内容的下拉列表.

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.

在更新其引用的列表时，有没有办法阻止单元格更改"宏运行?

Is there a way to prevent the "cell change" macro from running while I'm updating the list that it references?

希望这个问题有意义.

推荐答案

您可以使用Application.EnableEvents禁用Worksheet_Calculate事件，如下所示.请注意，这将禁用在Application.EnableEvents = False和Application.EnableEvents = True

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

因此，如果您的其他子对象是这样运行的-Worksheet_Calculate事件将不会触发

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

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

这篇关于VBA在更改单元格时运行宏，但在更改宏时不运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！