VBA 工作表更改事件绕过？

VBA Worksheet change event bypass?

提问by user1545643

I am fixing a spreadsheet. The programmer made a macro for each sheet to fire when the sheet is changed. This is good because it colour co-ordinates the sheet details when new information is added so I would like to keep this feature.

我正在修复电子表格。程序员为每个工作表制作了一个宏，以便在工作表更改时触发。这很好，因为它在添加新信息时协调工作表细节的颜色，所以我想保留这个功能。

I have written a macro which sorts the data and allows for removal and addition of new employees, this is in conflict with the change event macro and is causing my macro to have errors if they are both operational.

我编写了一个宏来对数据进行排序并允许删除和添加新员工，这与更改事件宏相冲突，如果它们都可操作，则会导致我的宏出错。

Q. Is there a way to bypass the worksheet change event while the macro is running and then have it in place again once the macro is finished?

问：有没有办法在宏运行时绕过工作表更改事件，然后在宏完成后将其重新设置到位？

Here is the code for the change event.

这是更改事件的代码。

Private Sub Worksheet_Change(ByVal target As Excel.Range, skip_update As Boolean)
If skip_update = False Then
    Call PaintCell(target)
End If
End Sub

My macro is bringing up errors when I refer to worksheets or ranges.

当我引用工作表或范围时，我的宏会出现错误。

回答by Dick Kusleika

I think you want the EnableEventsproperty of the Applicationobject. When you set EnableEventsto False, then nothing your code does will trigger any events and none of the other event code will run. If, for example, your code changes a cell it would normally trigger the Change event or the SheetChange event. However, if you structure it like this

我认为你想要对象的EnableEvents属性Application。当您设置EnableEvents为 False 时，您的代码所做的任何事情都不会触发任何事件，并且其他任何事件代码都不会运行。例如，如果您的代码更改了一个单元格，它通常会触发 Change 事件或 SheetChange 事件。但是，如果您像这样构建它

Application.EnableEvents = False
    Sheet1.Range("A1").Value = "new"
Application.EnableEvents = True

then changing A1 won't trigger any events.

那么改变 A1 不会触发任何事件。

Sometimes it's beneficial to have your code trigger event code and sometimes it's not. Use EnableEventswhen you want to prevent it.

有时让您的代码触发事件代码是有益的，有时则不然。使用EnableEvents时要防止它。

回答by ChrisB

This is an important modification to the answer from @Dick Kusleika.

这是对@Dick Kusleika 答案的重要修改。

When turning off the EnableEvents setting, it's a good idea to include error handling to turn EnableEvents back on. If you don't and the script throws an error, the change event trigger (your script) will stop working until you manually turn EnableEvents back on.

关闭 EnableEvents 设置时，最好包含错误处理以重新打开 EnableEvents。如果您不这样做并且脚本引发错误，则更改事件触发器（您的脚本）将停止工作，直到您手动重新打开 EnableEvents。

Ideally, you would place the following line immediately before any code that re-triggered your change event. The first line tells VBA to goto a label called "enableEventsOn" when it encounters an error. The second line bypasses change events.

理想情况下，您应该在任何重新触发更改事件的代码之前立即放置以下行。第一行告诉 VBA 在遇到错误时转到名为“enableEventsOn”的标签。第二行绕过更改事件。

On Error Goto enableEventsOn:
Application.EnableEvents = False

Then place this code immediately after code that re-triggered your change event. This turns change event triggers back on and returns normal error handling to your script.

然后立即将此代码放在重新触发更改事件的代码之后。这会将更改事件触发器重新打开并将正常的错误处理返回到您的脚本。

Application.EnableEvents = True
On Error Goto 0

Finally, place this code at the end of your script. It's a label referred to above. If an error is encountered between "On Error Goto enableEventsOn:" and "On Error Goto 0" then the script will go here and turn EnableEvents back on, leaving it ready to start the script the next time you change your worksheet.

最后，将此代码放在脚本的末尾。这是上面提到的标签。如果在“On Error Goto enableEventsOn:”和“On Error Goto 0”之间遇到错误，则脚本将转到此处并重新打开 EnableEvents，使其准备好在您下次更改工作表时启动脚本。

EnableEventsOn:
Application.EnableEvents = True

回答by mansuetus

Too risky in my own opinion.

在我看来太冒险了。

I'd use a variable, named "skip_update" that is set to "True" in the beginning of each macro and to false when it ends.

我会使用一个名为“skip_update”的变量，它在每个宏的开头设置为“True”，并在它结束时设置为 false。

e.g :

例如：

dim skip_update as Boolean = False

sub auto_macro_when_modify()
   if skip_update == True then
      exit sub ' NO CHANGE !
   end if
end sub

sub other_macro
    skip_update = True
    ' ( do stuff)
    skip_update = False
end sub

回答by Malcolm

You can hold down the CTRL key while doing an entry that would trigger the change on event routine. I have a routine to sum the values in a selected range in a given column. If I drag cursor from rows 1 to 5, highlighting them, then the sum of these is saved to the clipboard. Any entry in a cell in this column triggers the routine. If I hold down the CTRL key while doing the intended action or any entry in a cell in the column the routine is not initiated. May not work for more complicated ChangeEvent scenarios.

您可以在执行将触发事件例程更改的条目时按住 CTRL 键。我有一个例程来对给定列中选定范围内的值求和。如果我将光标从第 1 行拖到第 5 行，突出显示它们，那么它们的总和将保存到剪贴板。此列中单元格中的任何条目都会触发该例程。如果我在执行预期操作或列中单元格中的任何条目时按住 CTRL 键，则不会启动例程。可能不适用于更复杂的 ChangeEvent 场景。