I'm setting up a timer event based on Chip Pearson's code which is shown below.
This works fine when I run the StartTimer code. I'd like to have this code
run when the workbook starts - so I tried putting the code in the
"ThisWorkbook" module - but then the timer event doesn't work. Any help is
appreciated.


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, Procedu=cRunWhat, _
Schedule:=True
End Sub

'This stores the time to run the procedure in the variable RunWhen, two
minutes after the current time.

'Next, you need to write the procedure that will be called by OnTime. For
example,

Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
MsgBox "Hello"

StartTimer ' Reschedule the procedure
End Sub

Leave Chip's coding in a workbook module. On the ThisWorkbook sheet, place
this coding:

Private Sub Workbook_Open()
Call StartTimer
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"dhstein" wrote:

I'm setting up a timer event based on Chip Pearson's code which is shown below.
This works fine when I run the StartTimer code. I'd like to have this code
run when the workbook starts - so I tried putting the code in the
"ThisWorkbook" module - but then the timer event doesn't work. Any help is
appreciated.


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, Procedu=cRunWhat, _
Schedule:=True
End Sub

'This stores the time to run the procedure in the variable RunWhen, two
minutes after the current time.

'Next, you need to write the procedure that will be called by OnTime. For
example,

Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
MsgBox "Hello"

StartTimer ' Reschedule the procedure
End Sub

Luke,

Thank you - that worked perfectly.

David


"Luke M" wrote:

Leave Chip's coding in a workbook module. On the ThisWorkbook sheet, place
this coding:

Private Sub Workbook_Open()
Call StartTimer
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"dhstein" wrote:

I'm setting up a timer event based on Chip Pearson's code which is shown below.
This works fine when I run the StartTimer code. I'd like to have this code
run when the workbook starts - so I tried putting the code in the
"ThisWorkbook" module - but then the timer event doesn't work. Any help is
appreciated.


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, Procedu=cRunWhat, _
Schedule:=True
End Sub

'This stores the time to run the procedure in the variable RunWhen, two
minutes after the current time.

'Next, you need to write the procedure that will be called by OnTime. For
example,

Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
MsgBox "Hello"

StartTimer ' Reschedule the procedure
End Sub