How can I check for or cancel MULTIPLE pending application.ontime events in excel VBA?

Question

I'm using the Application.Ontime event to pull a time field from a cell, and schedule a subroutine to run at that time. My Application.Ontime event runs on the Workbook_BeforeSave event. As such, if a user (changes the desired time + saves the workbook) multiple times, multiple Application.Ontime events are created. Theoretically I could keep track of each event with a unique time variable.. but is there a way to check/parse/cancel pending events?

Private Sub Workbook_BeforeSave
    SendTime = Sheets("Email").Range("B9")
    Application.OnTime SendTime, "SendEmail"
End Sub

Private Sub Workbook_BeforeClose
    Application.OnTime SendTime, "SendEmail", , False
End Sub

So if I:
change B9 to 12:01, Save the workbook
change B9 to 12:03, Save the workbook
change B9 to 12:05, Save the workbook
change B9 to 12:07, Save the workbook
etc

I end up with multiple events firing. I only want ONE event to fire (the most recently scheduled one)

How can I cancel ALL pending events (or enumerate them at least) on the Workbook_BeforeClose event?

Answer 1

Each time you call Application.Ontime save the time the event is set to run (you could save it on a sheet or in a module scoped dynamic array)

Each time your event fires, remove the corresponding saved time

To cancel all pending event iterate through the remaining saved times calling Application.Ontime with schedule = false