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

I don't think you can iterate through all pending events or cancel them all in one shabang. I'd suggest setting a module level or global boolean indicating whether or not to fire your event. So you'd end up with something like this:

Dim m_AllowSendMailEvent As Boolean
Sub SendMail()
If Not m_AllowSendMailEvent Then Exit Sub

'fire event here

End Sub

Edit:

Add this to the TOP of the sheet module which contains the range which contains the date/time value you're after:

' Most recently scheduled OnTime event. (Module level variable.)
Dim PendingEventDate As Date

' Indicates whether an event has been set. (Module level variable.)
Dim EventSet As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SendTimeRange As Range

' Change to your range.
Set SendTimeRange = Me.Range("B9")

' If the range that was changed is the same as that which holds
' your date/time field, schedule an OnTime event.
If Target = SendTimeRange Then

    ' If an event has previously been set AND that time has not yet been
    ' reached, cancel it. (OnTime will fail if the EarliestTime parameter has
    ' already elapsed.)
    If EventSet And Now > PendingEventDate Then

        ' Cancel the event.
        Application.OnTime PendingEventDate, "SendEmail", , False

    End If

    ' Store the new scheduled OnTime event.
    PendingEventDate = SendTimeRange.Value

    ' Set the new event.
    Application.OnTime PendingEventDate, "SendEmail"

    ' Indicate that an event has been set.
    EventSet = True

End If

End Sub

And this to a standard module:

Sub SendEmail()

    'add your proc here

End Sub