CODEWITHSUNDEEP
excelvbaevents
Fred Cailloux
Fred Cailloux

Reputation: 195

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.

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.

Upvotes: 2

Views: 462

Answers (2)

WhiteTiger
WhiteTiger

Reputation: 23

Application.MoveAfterReturn = False

This leaves the cursor in the cell where it was. Thus, no second selection change event. The user must explicitly select another cell - this can be good or bad. Please take care: this is an Application level setting, so applied for all open workbooks. If you want to avoid it, then you must use the Workbook_Activate and Workbook_Deactivate events to set it to False and True respectively.

You comment on Apr 7, 2023 at 18:10 says you do not want selection change at all. Then do not use it, delete the code for it completely. Nothing will happen, put all your code into worksheet_change.

NB: The first selection_change occurs when the user selects the cell for editing, NOT after changing the value and pressing enter. After pressing enter the second selection_change occurs.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 3

Related Questions