AutoSave

[Memphis]

Hello,
I visited Chip Pearson's webpage regarding the "Scheduling Events With
OnTime ".
I found his solution to work well. http://www.cpearson.com/excel/OnTime.aspx
Now, what I want to do, is not have the process take place in a module, but
instead in a private sub.. For example, I have a master form from which
copies are made, and I don't need the Modules to be copied over to the "child
forms". Since the modules are not beign copied I thought I might be able to
insert this code, with a bit of tweaking, into a command button (cmdBegin).

So this is what I wrote:

Private Sub cmdBegin_Click()
Dim RunWhen As Double
Dim MSG As String
Dim cRunIntervalSeconds As Double
cRunIntervalSeconds = 30 ' Thirty Seconds
Dim cRunWhat As String ' "Note: deviated here from his code purely for
testing purposes"
cRunWhat = "TheSub" ' the name of the procedure to run

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

TheSub:

MSG = MsgBox("Hello World", vbOKCancel)
If MSG = vbOK Then
ActiveWorkbook.Save
GoTo StartTimer ' Reschedule the procedure
Else
Exit Sub
End If

StopTimer:
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

It works, but unfortunately once I click OK, it immediately prompts me to
save again. Unless I click cancel.
Now.. I put the VBOkCancel in the path to allow me to bail out of the enless
"saving loop".

Any thoughts?

Thank you

 

[Wouter HM]

Hi Memphis,

It look simple to me.
1:
Put this code in a module.


' -- start of code
Public RunWhen As Double
Public Const cRunIntervalSeconds = 2 ' 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()
MSG = MsgBox("Hello World", vbOKCancel)
If MSG = vbOK Then
ActiveWorkbook.Save
StartTimer ' Reschedule the procedure
End If
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub

' --- end of code

2: for the commandbutoon:

Private Sub cmdBegin_Click()
StartTimer
End Sub

 

[Memphis]

Thank You Wouter.
with your help I stiched this together
I found a small problem with the code you provided, this is where the
problem was, you had "2", and this number indicates the number of seconds and
not minutes, so 120 seconds = 2 minutes. ;-)

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120'
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()
ActiveWorkbook.Save
StartTimer ' Reschedule the procedure
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub