CODEWITHSUNDEEP
vbaexcel
rohrl77
rohrl77

Reputation: 3337

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

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.

Upvotes: 4

Views: 10773

Answers (1)

user3598756
user3598756

Reputation: 29421

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

Upvotes: 7

Related Questions