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

Question

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.

Application.EnableEvents = False works to prevent unwanted events from being triggered by your code. Maybe you can show us exactly how you used it (using the smallest possible amount of code required to replicate your problem) ? Otherwise, it's not really possible to reliably answer your question as applied to your specific use case. — Tim Williams
– Tim Williams, Commented Apr 7, 2023 at 17:21
This question is kind of a follow-up to my previous questions. stackoverflow.com/questions/75954494/… — Fred Cailloux
– Fred Cailloux, Commented Apr 7, 2023 at 17:26
I am starting to grasp the problem. Basically, I do some stuff in the SelectionChange event that I must avoid when the two events are registered in the VBA queue. — Fred Cailloux
– Fred Cailloux, Commented Apr 7, 2023 at 17:28

Tim Williams · Accepted Answer · 2023-04-07 18:33:08Z

3

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

edited Apr 7, 2023 at 18:33

answered Apr 7, 2023 at 17:55

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

1 Comment

Fred Cailloux Over a year ago

Yes, Application.EnableEvents = false will prevent the third triggering but until the first Change event commands the EnableEvents flag to OFF, EXCEL will have added the first SelectionChange event perpetrated by the user ENTER key. So instead of having 3 events I will have only 2. I want only one event. The one triggered by the Change in the original cell. I'm not sure if this is correctly explained. I'm a bit confused myself. (See edited code in the question)

WhiteTiger · Accepted Answer · 2024-10-27 21:49:41Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related