Excel VBA: Why does event trigger twice?

Question

I'm trying to avoid Event loops by disabling Events at crucial points. However, it doesn't always work. For instance, this code for a Combo box:

Private Sub TempComboS_Change()
Dim e
e = Application.EnableEvents
Application.EnableEvents = False
  ' 
Application.EnableEvents = e
End Sub

The blank line is where the useful code goes; as it stands it obviously doesn't do anything. However, when I run it this way (with the blank line), it reaches "End Sub", then it goes back to the beginning and runs again. (This would make the useful code run twice).

Why is this happening?

EDIT: To clarify for the folks who've been helping me.

I have a macro that opens the dropdown list of the Combo box, activates it, then ends. It works properly. When I select an item from the open list, the Change event runs. This is the current version of the change event:

Private Sub TempComboS_Change()
End Sub

I put a breakpoint on the Private Sub line. It shows that this Change event runs, then runs again. I suspect that it has been doing this all along, and I noticed it now because I need to add code here.

I have no class modules or userforms. The controls are on a worksheet.

I'm going to try the "Run Once" suggestion, and I'll let you know if it works.

---

I tried the "Run Once" code you suggested. It sort of works, but I seem to have a bigger issue. When I select a drop-down list from a data-validated cell, the TempComboS_Change event triggers -- but not only didn't I touch this combo box, the cell isn't the LinkedCell for the combo box. In other words, it seems to be triggering by actions unconnected to the combo box!

Got to find out about that Call Stack thing...

Answer 1

Private Sub cmbOrder_Change()
    If cmbOrder = "" Then Exit Sub

    Dim arr As Variant, maxorder As Integer
    arr = Range("rngOrder")
    maxorder = WorksheetFunction.Max(arr)
    Dim errmsg As String, err As Boolean
    err = False
    errmsg = "This value must be a whole number between 1 and " & maxorder + 1
    Dim v As Variant
    v = cmbOrder.Value
    If IsNumeric(v) = False Or (IsNumeric(v) = True And (v > maxorder + 1) Or v < 1) 
    Then
        MsgBox errmsg
        cmbOrder = ""
        err = False
    Else
        txtOrder.Value = cmbOrder.Value
    End If

End Sub

A bit late to the party but the problem of code repetition can be shown here in similar circumstances. Remove the first line of code and any error messages are dished out twice. This is because of the line that clears the ComboBox that is regarded as a change and picks up another error as null input is an error! May help someone with similar issue.

Answer 2

Here is a bit of code to help investigate "sequence of events" issues

In a Standard Module

Public Enum eNewLine
    No
    Before
    After
    Both
End Enum

Public Function timeStamp(Optional d As Double = 0, Optional newLine As eNewLine = No, Optional Indent As Long = 0, _
                            Optional Caller As String, Optional Context As String, Optional message As String) As String
Dim errorMessage As String

    If Err.number <> 0 Then
        errorMessage = "ERROR: " & Err.number & ": " & Err.Description
        Err.Clear
    End If
    If d = 0 Then d = Time
    With Application.WorksheetFunction
        timeStamp = .Text(Hour(d), "00") & ":" & .Text(Minute(d), "00") & ":" & .Text(Second(d), "00") & ":" & .rept(Chr(9), Indent)
    End With
    If Len(Caller) <> 0 Then timeStamp = timeStamp & Chr(9) & Caller
    If Len(Context) <> 0 Then timeStamp = timeStamp & ": " & Chr(9) & Context
    If Len(message) <> 0 Then timeStamp = timeStamp & ": " & Chr(9) & message
    Select Case newLine
    Case Before
        timeStamp = Chr(10) & timeStamp
    Case After
        timeStamp = timeStamp & Chr(10)
    Case Both
        timeStamp = Chr(10) & timeStamp & Chr(10)
    Case Else
    End Select
    If Len(errorMessage) <> 0 Then
        timeStamp = timeStamp & Chr(9) & errorMessage
    End If

End Function

At the top of each Module

'Module level Trace Hearder
Const debugEvents as Boolean = True
Const cModuleName As String = "myModuleName"
Const cModuleIndent As Long = 1

You can assign a module level indent for each module to organise the hierarchy an make it easy to understand.

In each Sub or Function (or property if you need)...

sub mySubName()
Const cMyName As String = "mySubName"

If debugEvents Then Debug.Print timeStamp(NewLine:=Before,Indent:=cModuleIndent, Caller:=cModuleName, Context:=cMyName, Message:="Start")

'Do stuff

If debugEvents Then Debug.Print timeStamp(NewLine:=After,Indent:=cModuleIndent, Caller:=cModuleName, Context:=cMyName, Message:="End")
End Sub

...Or you can use Me.Name for the Context if its a form or a sheet etc. and you can put whatever message or variable values you like in the Message.

You can also use a Timer (eg MicroTimer) and put the result in the Message section.

Here is an example output:

15:54:07:       Roll-Up Select:     Worksheet_Activate:      Start: 3.24591834214516E-03


15:54:07:           cDataViewSheet:     Class_Initialize:   Start

15:54:07:               cRevealTarget:  Class_Initialize:   START
15:54:07:               cRevealTarget:  Class_Initialize:   END

15:54:09:           cDataViewSheet:     startTimer:     : START
15:54:09:           cDataViewSheet:     startTimer:     init Timer
15:54:09:               cOnTime:    Class_Initialize
15:54:09:               cOnTime:    Let PulseTime:  Inheret PulseTime from host sheet
15:54:09:           cDataViewSheet:     startTimer:     : END

15:54:09:       Roll-Up Select:     Worksheet_Activate:      END:   1.38736216780671

Answer 3

The Combobox_Change() will fire whenever there is a change in the combobox. For example

Option Explicit

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub CommandButton1_Click()
    '~~> If something is selected in the combo then
    '~~> this line will cause ComboBox1_Change to fire
    ComboBox1.Clear
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
End Sub

So if you load the userform and select an item ComboBox1_Change will fire. You then use the commanbutton to clear the combo the ComboBox1_Change will again fire.

There is one more scenario when the change will again fire. When you change the combobox from the ComboBox1_Change event itself. Here is an example. And I believe this is what is happening in your case.

Scenario 1

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
    ComboBox1.Clear
End Sub

Scenario 2

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
    ComboBox1.AddItem "Bah Blah Blah"
End Sub

Private Sub ComboBox1_Change()
    MsgBox "A"
    ComboBox1.ListIndex = 1
End Sub

In the first scenario you can getaway with

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
End Sub

Private Sub ComboBox1_Change()
    If ComboBox1 <> "" Then
        MsgBox "A"
    End If
End Sub

In the 2nd Scenario, you can use something like this

Dim boolRunOnce As Boolean

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Bah Blah"
    ComboBox1.AddItem "Bah Blah Blah"
End Sub

Private Sub ComboBox1_Change()
    If boolRunOnce = False Then
        MsgBox "A"
        boolRunOnce = True
        ComboBox1.ListIndex = 1
    Else
        boolRunOnce = False
    End If
End Sub