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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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.
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=TimeValue("00:02:00"), _
        Procedure:="EnterPmLoads", Schedule:=False
End Sub
And...
Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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. :confused:
Thanks for looking into it though.

(Using XL2003)
Dan
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Thanks Richie,
I'm ready to try anything. (Including a little voo-doo if necessary), but I'll try your suggeston first.

Dan
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top