chip pearson are you there pls, problem with your RunWhen

[kk]

Hi, a relative newbie here, I have dutifully cut and pasted your
runwhen stuff from an earlier thread and your site, and have a
problem;

Sub timeron()

'run it at the time in brackets;
RunWhen = CLng(Now) + TimeValue("16:46:00") ' 11:20 PM today
'RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat,
schedule:=True
End Sub

I use remark comma to switch from either the first RunWHen or the
second. When i do the second, the procedure cRunWhat runs the
specified 10 seconds after I have F8 tabbed thru it. When i rem out
the second RunWhen and use the first, then F8-tab right thru, nothing
happens. I've looked at the watch window, and RunWhen gets a long
number, eg now it is 37884.7041666667, and i have noticed that once i
have gone right thru the thing and then go to restart it, Runwhen
re-opens with the same value as soon as i start the sub, i.e. it is
already populated before i get to the runwhen line. Am i doing
something very obviously very wrong? Where I am hoping to get to by
the way is to set Runwhen so that at e.g. 0815 hours every day, or
even better every weekday, the thing runs automatically. will the
first runwhen line above reset itself to the same time every day
please?

thanks

k

 

[Chip Pearson]

If you want the procedure to execute every day at 8:15 AM, just use

RunWhen = Date + 1 + TimeSerial(8, 15, 0)

already populated before i get to the runwhen line.

RunWhen is declared as a global variable, and retains its value even after
the procedure has ended.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com (e-mail address removed)