How to prevent ComboBox Change event from being called when source list is changed

Question

TL;DR: How to prevent ActiveX ComboBox from being triggerd when adding or deleting items in the source list

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.

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

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

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.

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

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.

Here is one way to do it: remove the current selection / value from the ComboBox by issuing a ComboBox1.Value = vbNullString in the first line of Sub TestAddItem() (just before the rest of your code). — Ralph
B2 must be empty too (as the linked cell). dropbox.com/s/x2v6ci3lxrf13fb/… — Ralph
Ok. With B2 empty this works. Unfortunately, I have other calculations that depend on that... ofcourse I can work around that, but I'm trying to avoid the headache. You have given me at least one more workable solution thank you! PS: I can't access dropbox from my current IT setting, will do so at home though. — rohrl77

user3598756 user3598756 · Accepted Answer · 2016-10-12T08:23:46

edited after OP's comment

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

How to prevent ComboBox Change event from being called when source list is changed

1 Answers