VBA工作表更改事件绕过? [英] VBA Worksheet change event bypass?

问题描述

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

我编写了一个宏，该宏对数据进行排序，并允许删除和添加新员工，这与更改事件宏冲突，并且如果我的宏都正常运行，则会导致我的宏出现错误.

问有没有一种方法可以在宏运行时绕过工作表更改事件，然后在宏完成后再将其放回原处?

这是更改事件的代码.

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

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

解决方案

我认为您需要Application对象的EnableEvents属性.当您将EnableEvents设置为False时，您的代码不会执行任何操作，也不会触发任何事件.例如，如果您的代码更改了单元格，则通常会触发Change事件或SheetChange事件.但是，如果您采用这种结构

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

然后更改A1不会触发任何事件.

有时候让代码触发事件代码是有益的，有时却不是.要防止出现此情况，请使用EnableEvents.

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.

解决方案

I think you want the EnableEvents property of the Application object. When you set EnableEvents to 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

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

then changing A1 won't trigger any events.

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

这篇关于VBA工作表更改事件绕过?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！