Is there a way to programmatically clear the EXCEL VBA Event queue?

Is there a way to programmatically clear the EXCEL VBA Event queue? In EXCEL VBA, when a worksheet cell is changed and then the user hit ENTER to commit the cell editing, there are two events taking place at the worksheet level. They are : • Private Sub Worksheet_Change(ByVal Target As Range) • Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Inevitably, these two events are recorded in a queue and the priority is given to the Worksheet_Change, then the Worksheet_SelectionChange is executed. The Change event is triggered because the cell content has been changed. Then the SelectionChange event is triggered because the user hit the ENTER key, changing the selection to a cell under the edited cell. These two steps generate two events.

I made this code that catch a change in a particular cell and I need to completely avoid the following event, Worksheet_SelectionChange, to run after Worksheet_Change sub terminates. That is because in my SelectionChange sub there are other important jobs done and they must not intervene when the cell content is modified.

Surely, there must be a way to prevent SelectionChange to take place. I tried to find some kind of VBA function that would empty the EXCEL Event queue list within the Worksheet_Change sub. This doesn’t seem to exist at the moment. I also tried to utilize the Application.EnableEvents = False, but this will only take effect after both events are added to the queue, which will not prevent the awaiting SelectionChange event to execute subsequent to the Change event.

The Question: Is there a way to clear the EXCEL VBA Event queue programmatically?

Some kind of function that would look like Application.ClearEventQueue, so as to prevent the execution of any subsequent unrealized events. This function could be called in the Change sub and would prevent the execution of any further awaiting events. How could we implement such function in VBA?

Dim NewLocation As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    Set NewLocation = Target.Offset(0, 1) ' At this location a work is written in the cell
    NewLocation.Select ' Here the selection is changed and EXCEL trigger a third event, SelectionChange
    Debug.Print "still in change event", NewLocation.Row, Target.Value
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Debug.Print "selectionchange event", Target.Row, Target.Value
End Sub

This code will show that when a user enter a new value to the cell, there will be 3 events taking place.

first a SelectionChange event caused by the line: NewLocation.Select
Second a Change event because the user changed the cell and hit ENTER
Third Another SelectionChange caused by the user hit ENTER so the focus goes to the cell just under (only temporarily, until the code command NewLocation.Select.

I'm trying to avoid the third SelectionChange to execute. Which is inevitable because using Application.EnableEvents = False could only take effect after EXCEL captured the first ENTER step. So with EnagleEvents set to OFF only two events will take place. Ideally I would require that even the previous SelectionChange event be cancelled.

Solution

Application.EnableEvents = False
NewLocation.Select 
Application.EnableEvents = True

will prevent the Select from triggering another event

EDIT: there doesn't seem to be any way to clear the event queue, so the best you could do might be to disregard Selection_Change events which occur immediately after a Change event handler exits:

Option Explicit

Dim NewLocation As Range
Dim tChange As Double

Private Sub Worksheet_Change(ByVal Target As Range)
    Set NewLocation = Target.Offset(0, 1) ' At this location a work is written in the cell
    Application.EnableEvents = False
    NewLocation.Select
    Application.EnableEvents = True
    Debug.Print "still in change event", NewLocation.Row, Target.Value
    tChange = Timer  'note exit time
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Timer - tChange > 0.05 Then 'is this immediatley following exit from a Change event?
        Debug.Print "selectionchange event", Target.Row, Target.Value, Timer - tChange
    Else
        Debug.Print "Skipped handling selection change"
    End If
End Sub