On time Event

[ExcelMonkey]

I am trying to program an alarm like this:

Sub SetAlarm()
Application.OnTime TimeValue("12:12:00 pm"), "DisplayAlarm"
End Sub

Sub DisplayAlarm()
Beep
MsgBox "Wake up. Its time for your afternoon break!"

End Sub

However it never seems to go off. I have put in a regular module.
What have I done wrong?

Thank-yo

 

[pikus]

In order for the application to check the time SetAlarm() has to b
running. If you start SetAlarm() you can have it WAIT until that time
but that probably won't work for you. Search the newsgroup and yo
should find several explanations of how to do exactly what you'r
asking about. - Piku

 

[Charles]

ExcelM,

Here is another example and it works.

Sub SetAlarm()
Application.OnTime Now + TimeValue("00:01:00"), "DisplayAlarm"
End Sub

Sub DisplayAlarm()
Beep
MsgBox "Wake up. Its time for your afternoon break!"

End Sub


Charle

 

[ExcelMonkey]

The reason it wasn't working is that I did not run it. I forgot it wa
in a module which actually needs to RUN. I was sitting there waitin
for it to engage like a dummay!!!!!

Yes Charles yours works too WHEN RUN.

Thanks

 

[Tom Ogilvy]

SetAlarm doesn't have to be running for this to work. It has to have been
run one time to set the event.

Sub SetAlarm()
Application.OnTime TimeValue("2:43:00 pm"), "DisplayAlarm"
End Sub

Sub DisplayAlarm()
Beep
MsgBox "Wake up. Its time for your afternoon break!"

End Sub

worked fine for me.

If Excel had the focus the message pop'd up in the foreground

If Excel did not have the focus, the Excel icon on the taskbar turned blue
and pulsed. Activating excel revealed the message pop'd up.

Excel 2000, Windows 2000

 

[ExcelMonkey]

Another question. What if I wanted this to run every minute. The cod
below when ran will excute once i.e. 1 minute after NOW. However i
does not run again. In order to dot this, do I have to make th
SetAlarm sub call itself befor the EndSub line (i.e. recursiv
programming)?

Thanks



Sub SetAlarm()
Application.OnTime Now + TimeValue("00:01:00"), "DisplayAlarm"
End Sub

Sub DisplayAlarm()
Beep
MsgBox "Wake up. Its time for your afternoon break!"

End Su

 

[Chip Pearson]

See www.cpearson.com/excel/ontime.htm for example code.


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

 

[ExcelMonkey]

So Chip, I used teh following code from your site. But it only ran onc
as opposed to every minute.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "The_Sub"

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
MsgBox "Wake up. Its time for your afternoon break!"
End Su

 

[Tom Ogilvy]

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "The_Sub"

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
MsgBox "Wake up. Its time for your afternoon break!"
End Sub

Sub StopIt()
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=False
End Sub

 

[Chip Pearson]

You need to set RunWhen and then call OnTime from within The_Sub.

 

[ExcelMonkey]

thanks Tom. I guess I could call the StartTimer sub again at the end o
the final sum too.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "The_Sub"

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub The_Sub()
MsgBox "Wake up. Its time for your afternoon break!"
StartTimer
End Su