Ontime Event Going haywire at midnight

[Drumin_Phreak]

Hello! I have a excel project that starts an ontime event on workbook open. This thing works fantastic! However, when midnight rolls around it crashes. After doing some research I have surmised that the problem is when the date changes, the time is now After the initial time set. I tried to include the now function (dTime2= Now + TimeValue ("00:05:00") and this does nothing. The one that currently works is:

dTime2 = Time + TimeValue("00:05:00")
  Application.OnTime dTime2, "AutoSaves"

I have been struggling with this for quite sometime. If I could get any help it would greatly be appreciated.

 

[Norie]

When do you want 'AutoSaves' to run?

 

[Drumin_Phreak]

When do you want 'AutoSaves' to run?

Every 5 minutes (this one book could be open for days at a time also)

 

[Drumin_Phreak]

*Bump (I think I posted this in a bad time of day)

 

[RoryA]

Use Now rather than Time, since that will include a Day part.

 

[Drumin_Phreak]

Use Now rather than Time, since that will include a Day part.

Thank you for the relpy. As I previously stated, when I try to use now, the script won't launch at all..

 

[Drumin_Phreak]

Open Resumption of Midnight shift, I did indeed try the "NOW" function so my code is

[COLOR=#333333]dTime2 = Now + TimeValue("00:05:00")
[/COLOR][COLOR=#333333]  Application.OnTime dTime2, "AutoSaves"[/COLOR]

However, when the hour struck Midnight it froze up and went into an infinite loop trying to save.

My next idea is:

Can I some how put a timer when the workbook is opened to suspend auto saving at like 23:45 and Resume saving at 00:05 the next day?

 

[Drumin_Phreak]

So it seems like no matter what I do it still goes crazy right at midnight, they do a reload and it works perfect from there on out.

 

[RoryA]

I can't see why it would do what you're describing but as a workaround, you could do something like this:

dTime2 = Now + TimeValue("00:05:00")
if Hour(dTime2) = 23 and Minute(dTime2) >=50 then dTime2 = Date() + 1 + TimeValue("00:05:00")

 

[Drumin_Phreak]

I can't see why it would do what you're describing but as a workaround, you could do something like this:

dTime2 = Now + TimeValue("00:05:00")
if Hour(dTime2) = 23 and Minute(dTime2) >=50 then dTime2 = Date() + 1 + TimeValue("00:05:00")

RoryA,

I sincerely appreciate your suggestions, but nothing I do seems to help this situation. I am so confused about what i can do to fix this issue.