Turning off an App.OnTime (preset time of day) macro

[HalfAce]

OK, I have an Application.OnTime situation that's kicking my a$$ and I'm ready to concede defeat.

I know what the problem is, and what needs to be done to solve it, but can't get it to work.

I'm using the following in my Workbook_Open and it works great.

Private Sub Workbook_Open()
Application.OnTime TimeValue("00:02:00"), "EnterPmLoads"
End Sub

This workbook is being used as a template file that gets saved under a different name every day. Now as we all know, it will open the workbook if needed to perform this routine at two minutes after midnight and since I can't have hunderds of workbooks trying to do this all at once, I need to turn the OnTime routine off when the workbook gets closed. That's where I'm having the problem.
I have tried many variations including the following, which, either one it seems to me, should work.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:02:00"), _
        Procedure:="EnterPmLoads", Schedule:=False
End Sub

And...

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=Now(), _
        Procedure:="EnterPmLoads", Schedule:=False
End Sub

The error I'm getting when I try to close the workbook is:
Run-time error '1004':
Method 'OnTime' of object '_Application' failed

Can someone please tell me where I'm going wrong?

Thanks very much,
Dan

 

[Chitosunday]

This code is working fine in excel 2000

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:02:00"), _
Procedure:="EnterPmLoads", Schedule:=False
End Sub

You must have cancelled it twice. See your other code.

 

[HalfAce]

Hi Chitosunday,
Nope. Those are just two examples (of many variations) I've tried. All being tried one at a time. I just can't see why it won't work.
Thanks for looking into it though.

(Using XL2003)
Dan

 

[Richie(UK)]

Hi Dan,

The (first) code looks as if it should work (as confirmed by Chitosunday).

One thing I would suggest is that you use Public variables to hold details of the start time and the procedure to be run. That way, there can be no confusion between the setting and the canceling routines.

 

[HalfAce]

Thanks Richie,
I'm ready to try anything. (Including a little voo-doo if necessary), but I'll try your suggeston first.

Dan