Time Macro Issue

[Elton Law]

Dear Expert,
I asked for this question before.
Someone asked me to refer to this path.
http://www.brainbell.com/tutorials/ms-office/excel/Run_A_Macro_At_A_Set_Time.htm
I did it ....
I even tried to prepare similar marco in my Excel.
Save these 2 macros in a file called TimeMarco.Xls
The scripts (task) is meaningless ... Just type as a test.
I open this file TimeMarco.xls
But it works sometimes ... it does not work sometimes ....
I am using Excel 2000.
How I can make it work all the time ?
Thanks ...


Private Sub Workbook_Open()

Application.OnTime TimeValue("19:13:40"), "MyMacro"

End Sub

Sub MyMacro()

Application.OnTime TimeValue("19:13:40"), "MyMacro"

Application.Goto Reference:="R1C1"
ActiveCell.FormulaR1C1 = "I love you"
Range("A1").Select
Selection.Copy
Range("A2:A18").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
ActiveCell.FormulaR1C1 = "I hatee you"
Range("C1").Select
Selection.Copy
Range("C2:C11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E1").Select
ActiveCell.FormulaR1C1 = "I look ………"
Range("E2").Select

End Sub

 

[Dave Peterson]

Chip Pearson has lots of info here:
http://www.cpearson.com/excel/OnTime.aspx

 

[Elton Law]

Hi Dave,
I did see this web before.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub


Can you tell me how to type the starting time rather than "now" please?

 

[Dave Peterson]

Sub StartTimer()
RunWhen = TimeSerial(19,13,30)
....

Remember if you don't want the timer reset (just run the macro once), then don't
call the starttimer at the end of the TheSub procedure.