Run macro based on time

[Guest]

I am using a work sheet that links by DDE to a machine PLC.
It is updating once a second. I have a time of day clock built into the sheed.
How can I run a worksheet "Print" macro based on time of day from a selected
cell?

 

[Chip Pearson]

If the time to run the macro is in cell A1, use

Application.OnTime Range("A1").Value,"Print"

where Print is the name of the macro to run.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Check out Excel's Application.OnTime method. I have never used it personally
but it supposedly can display alarms, print things, do whatever, whenever
certain time is reached. You may want to use the TimeValue function along
with the method for determining the time you want the thing to print.

Hope this helps.

Bill Horton

 

[Guest]

Something like the following (but changed to print stuff)

Sub SetAlarm()
Application.OnTime 0.625, "DisplayAlarm"
End Sub

Sub DisplayAlarm()
Beep
MsgBox "Wake up. It's time for your afternoon break!"
End Sub

Taken from Excel VBA Programming For Dummies

Hope this helps.

Bill Horton

 

[Chip Pearson]

Application.OnTime 0.625, "DisplayAlarm"

While the above code will work just fine, it would be better to
use TimeSerial or TimeValue to get the appropriate time. Unless
you know how Excel treats time, the 0.625 is meaningless. Good
programming practice would be

Application.OnTime TimeSerial(15,0,0), "DisplayAlarm"
or
Application.OnTime TimeValue("15:00:00"),"DisplayAlarm"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




in message

 

[Guest]

Chip
We are using excel 2002. Not sure it includes Application On Time.
How can I tell
Thanks

 

[Chip Pearson]

Bruce,

The OnTime method exists in Excel 2002.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Chip
I am new to code
Here is what I have

Sub SetAlarm()
Application.OnTime Range("C5").Value("8:19:00"), "autoprint"
End Sub

The autoprint macro works from a button in the worksheet but not in the code
Do I need to do something in the worksheet to turn it on?
Thanks