Pause Events in Excel VBA

Question

I have an application written in VBA for Excel that takes in a live data feed. Whenever there is a change in the data, various events are triggered within VBA.

I also have some UserForms with ComboBoxes on them. My problem is that when I click the down arrow on the ComboBox and try to make a selection, the moment I get an update from the data feed, the ComboBox resets. What I would like to do is pause the events while I'm making my selection in the ComboBox and unpause it when I'm done.

I feel like this should be a simple thing to do but I'm just not sure which functions I should be putting my code into. Any help would be greatly appreciated. Thanks.

Answer 1

Try this to turn off:

application.enableevents = false

And this to turn back on:

application.enableevents = true

Answer 2

Maybe you can put a flag to bypass the update event on your combobox until a selection is made.

Private bLock as boolean  ' declare at module level

' When a user clicks on the combobox
Private Sub DropDownArrow_Click()  ' or cboComboBox_Click()
    bLocked = True
End Sub

' This procedure is the one that does the updating from the data source.
' If the flag is set, do not touch the comboboxes.
Private Sub subUpdateComboBoxes()
    If Not bLocked then
        ' Update the comboboxes
    End If
End Sub


' When the selection is made, or the focus changes from the combobox.
' check if a selection is made and reset the flag.
Private Sub cboComboBox_AfterUpdate()  ' Or LostFucus or something else
    if Format(cboComboBox.Value) <> vbNullString Then
        bLocked = False
    End If
End Sub

Hope that helps.