Excel VBA ActiveX combobox change event infinite loop

Question

Let me preface by saying I am a self taught novice at VBA or coding for that matter.

I have a combobox "cmbStyle" not in a userform but diretly on a worksheet named "Cost". I need to run code on the change event of "cmbStyle". The problem I have is that when the user changes the combobox the event fires and the code runs but at the end of the code the combobox event fires again and so on and so on.

I know that Application.EnableEvents = False will have not effect on ActiveX controls so this is not a solution.

I have found descriptions on how to use a boolean variable to stop the looping in the change event for listboxes but I can't seem to get it to work in my instance.

My code will end the subroutine after the Cange Event fires the second time. However, the next time the user selects another value from the Combobox the CodeDoneRun variable is still TRUE so the subroutines won't run again when I need it to.

I feel I am missing something very basic here....

My code is as follows:

Public CodeDoneRun as Boolean

Private Sub cmbStyle_Change()
    If CodeDoneRun = True Then Exit Sub

    CodeDoneRun = True

    Call other Subroutines

End Sub

You need to set your re-entry flag back to false after Call other Subroutines. — Comintern
– Comintern, Commented Oct 29, 2016 at 16:31

user6432984 · Accepted Answer · 2016-10-29 18:28:40Z

0

Enclose your function call in the if statement and let the code run through.

Public CodeDoneRun as Boolean

Private Sub cmbStyle_Change()
    If Not CodeDoneRun Then
         Call other Subroutines
    End If

    CodeDoneRun = Not CodeDoneRun

End Sub

answered Oct 29, 2016 at 18:28

user6432984

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA ActiveX combobox change event infinite loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related