Where to cancel Application.OnTime in VBA

Question

Using VBA in Excel 2003, I'm trying to cancel an Application.OnTime event using the following code:

Application.OnTime EarliestTime:=varNextRunTime, Procedure:="SomeMethod", Schedule:=False

where varNextRunTime is a global variable containing the next time it is due to run. This code runs in the Workbook_BeforeClose event handler so is always run when the workbook is closed, which is my intention.

However, if the user tries to close the workbook, but changes their mind and hits the cancel button when prompted to Save (Yes, No, Cancel), the Application.OnTime event is still cancelled. BeforeClose is always run before they decide to hit cancel, so has anyone got any ideas how I can only cancel the Application.OnTime event when the workbook is closed?

Answer 1

Investigate using:

Application.Quit

If you find this command results in the Excel program remaining open although the document has closed, you may want to follow with

ActiveWorkbook.Close False

I'm not in position to test this or give more insights, unfortunately.

Answer 2

Check the Saved property of the Workbook in your event handler. If the workbook is unsaved then display your own dialog to find out if the users wants to save changes, not save changes or cancel.

Here's some rough code. Obviously uncomment the line which deals with the Application.OnTime part and change the MsgBox title to something suitable

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim response As Integer

If Not (Me.Saved) Then
    response = MsgBox("Do you want to save changes to '" & Me.Name & "'?", vbYesNoCancel, "put title here")

    If (response = vbCancel) Then
        Cancel = True
    ElseIf (response = vbYes) Then
        Me.Save
    End If
End If

If Not (Cancel) Then
    ' Application.OnTime EarliestTime:=varNextRunTime, Procedure:="SomeMethod", Schedule:=False
End If

End Sub

Answer 3

Here's some ideas

http://www.dailydoseofexcel.com/archives/2004/06/16/beforeclose-vs-beforereallyclose/