多个Worksheet_change事件相互触发 [英] Multiple Worksheet_change events firing each other

问题描述

我有一个工作簿，在工作表中有多个Worksheet_Change事件。
这些宏均与一个活动的x组合框相关。

I have a workbook with multiple Worksheet_Change events in the sheet. Each of these macroes are related to an active x combobox.

问题在于，当我更改其中一个组合框时，宏会触发（如预期的那样），从而启动另一个宏（不应运行）。我已经设置了Application.EnableEvents = False
但是问题可能是我正在更改一个cell.value，它链接到另一个组合框，因此也链接到另一个worksheet_change事件。

The problem is that when I change one of the comboboxes the macro fires (as expected), which in turn start another macro (which is not suppose to run). I have already set the Application.EnableEvents = False But the issue might be that I am changing a cell.value, which is linked to another combobox and hence also linked to another worksheet_change event.

我看到的一种解决方法是，如果组合框是实际选择的，则只能运行宏，但这是第二个问题。我找不到让vba返回活动组合框名称的方法。

A workaround as I see it, might be to only run the macro, if the combobox is the one actually selected, but here comes the second problem. I can't find a way to have vba return the name of the active combobox.

请注意，这些组合框未连接到用户表单，只是直接放置在工作表上。

Please note that these comboboxes is not connected to a userform, they are simply placed directly on the worksheet.

有人对如何解决这个问题有任何想法吗？

Is there anybody who has any idea on how to solve this??

任何帮助都是值得赞赏的，

Any help is much appreciated,

推荐答案

您还可以设置全局变量处理事件。您可以在每个更改事件开始时进行检查。

You could also set a global variable handling events. You check it at the beginning of each change event.

Dim ufEventsDisabled As Boolean

Sub YourSub()
    ufEventsDisabled = False
    Range("A1").Value=1 'This triggers the event

    ufEventsDisabled = True
    Range("A1").Value=1 'This doesn't trigger the event

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If ufEventsDisabled=True Then Goto ExitEvent:
    'Your regular worksheet code

ExitEvent:
    ufEventsDisabled=False
End Sub

这篇关于多个Worksheet_change事件相互触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！