Time Macro Issue

E

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
 
E

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Filter 2
Different Results from the Same Macro 3
Help me5 1
Cleanup this macro please 3
Help me4 2
Totals An Easier Way?? 5
Copy and paste range ontime 2
Need Help with a VBA subroutine 0

Top