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...

Try removing Dim e e = Application.EnableEvents and replace Application.EnableEvents = e with Application.EnableEvents = True. Re-run the code. Does it still run twice? — WGS
– WGS, Commented Nov 29, 2013 at 5:09
If it truly hits End Sub then your TempComboS_Change() is being called twice from another part of your code. Debug and step through your calling code. Should be able to tell where the second call is coming from. You can also verify this by removing all code from this sub and just put a msgbox in to see if it gets called twice. — Automate This
– Automate This, Commented Nov 29, 2013 at 5:24
In fact, if I stop the execution partway through, it immediately starts again. <br/>Portland: There is no calling code. For this test I've been running it either by changing the Combo box, or by running the procedure in the IDE window.<br/>Here's something else weird: I inserted a line "TempComboS.Visible = False" to hide the combo box, but it doesn't work. — Shawn V. Wilson
– Shawn V. Wilson, Commented Nov 29, 2013 at 6:42
Regardless of why it is firing twice, Application.EnableEvents does not apply to userform controls. — Excel Developers
– Excel Developers, Commented Nov 29, 2013 at 15:33

Cool Blue · Accepted Answer · 2013-12-02 15:05:25Z

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

johnbirt · Accepted Answer · 2020-06-10 16:55:04Z

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.

Siddharth Rout · Accepted Answer · 2013-11-29 10:50:22Z

0

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

answered Nov 29, 2013 at 10:50

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

4 Comments

Shawn V. Wilson Over a year ago

As you can see in my code snippet, there is no code in the change event that affects the combo box. In fact, it's running twice when there is no code in the Change event at all.

Siddharth Rout Over a year ago

I would like to see your workbook if possible? If yes, then can you upload the same in www.wikisend.com and share the link here?

Shawn V. Wilson Over a year ago

I'm not sure I can get permission to send the whole workbook. I don't suppose there's something in particular I can show you?

GUI Junkie Over a year ago

You can improve this code by using the Static keyword to declare the boolRunOnce inside the sub (if you're certain this code will always trigger twice)

Collectives™ on Stack Overflow

Excel VBA: Why does event trigger twice?

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related