CODEWITHSUNDEEP
vbaexcel
Dr Zimmerman
Dr Zimmerman

Reputation:

Where to cancel Application.OnTime in VBA

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?

Upvotes: 0

Views: 7633

Answers (4)

dymoacon
dymoacon

Reputation: 1

A bit late to the show but here is a simple solution that I've come across (and tested):

If a user deactivates the workbook by closing it, the workbook will still remain the ActiveWorkbook when the Workbook_WindowDeactivate event fires. If the user deactivates the workbook by switching to another workbook, then the new workbook will become the ActiveWorkbook by the time Workbook_WindowDeactivate fires. You can use this behavior to determine the action that caused the event to fire:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    If Application.ActiveWorkbook.Name = Me.Name Then
        'Your code here
    End If
End Sub

Upvotes: 0

barrowc
barrowc

Reputation: 10679

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

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

Here's some ideas

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

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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.

Upvotes: 0

Related Questions