如何防止在更改源列表时调用ComboBox Change事件 [英] How to prevent ComboBox Change event from being called when source list is changed

问题描述

TL; DR:如何在添加或删除源列表中的项目时防止触发ActiveX ComboBox

请注意，我已经看过这篇文章和这不是一个相同的问题，或者它不能为我当前的项目提供可行的解决方案.

Please note that I have seen this post and it is not the same problem or rather it does not provide a workable solution for my current project.

您可以通过创建ActiveX ComboBox并在工作表上的A1:A4，输出单元格B2中为其提供源列表范围，然后添加以下代码来复制问题:

You can replicate the problem by creating an ActiveX ComboBox and giving it a source list range on the sheet from A1:A4, Output cell B2, then adding this code:

Private Sub ComboBox1_Change()
    MsgBox ("hello")
End Sub

Sub TestAddItem()
    Range("A4").Insert xlDown
    Range("A4").Value = "NewItem"
End Sub

如果运行TestAddItem，则在转到新建项"行时，将触发组合框的Change事件.

If you run TestAddItem, upon getting to the "New Item" line, the Change event for the combo box will be triggered.

我已经找到了解决方法，但是只能找到解决方法建议，以添加一个布尔变量，该布尔变量检查是否在change事件中实际执行代码.这是我的示例中的样子:

I have searched for solutions to this, but could only find the workaround suggestion to add a Boolean Variable that checks whether or not to actually proceed with the code in the change event. Here is what that would look like in my example:

Option Explicit
Public bMakeItHappen As Boolean

Private Sub ComboBox1_Change()
    If bMakeItHappen Then
        MsgBox ("hello")
    End If
End Sub

Sub TestAddItem()
    bMakeItHappen = False
    Range("A4").Insert xlDown
    Range("A4").Value = "NewItem"
End Sub

如果更改组合框的目的地，则会发生相同的情况.手动在源范围中添加或删除项目不会触发事件.

The same thing happens if the destination of the combo box is changed. Adding or deleting items from the source range manually does not trigger the event.

注意:Application.EnableEvents = False对ActiveX元素没有影响！他们仍然会开火(根据Microsoft的设计)

Note: Application.EnableEvents = False has no impact on ActiveX elements! They will fire anyway (by design from Microsoft)

如何防止这种情况发生?就目前而言，该ActiveX元素不可用，或者如果我需要在代码的各处设置布尔变量，至少我不能使用它. /p>

How do I prevent this from happening? As it stands, this ActiveX element is unusable, or at least I can't use it if I need to set Boolean variables everywhere in my code.

推荐答案

在OP评论后进行了编辑

edited after OP's comment

由于涉及事件 timing ，因此它在工作表代码窗格中满足以下代码:

since involved events timing, it then suffices the following code in your worksheet code pane:

Option Explicit

Dim bMakeItHappen As Boolean

Private Sub ComboBox1_DropButtonClick()
    bMakeItHappen = True
End Sub


Private Sub ComboBox1_Change()   
    If bMakeItHappen Then
        MsgBox ("hello") '<-- i.e. the code to be executed at "actual" combobox value change
        bMakeItHappen = False
    End If
End Sub

这篇关于如何防止在更改源列表时调用ComboBox Change事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！