Using a time Variant with OnTime

[Mark Dullingham]

I've been trying to use Ontime to save a file at an interval triggered from a
toggle button. I could get it to work ok if I use Now + Timeserial (x,x,x)
but then couldn't stop it .
I have read loads of posts on this site and others about the need for a time
Variant to store the exact time so I have this

Option Explicit

Public nTime As Variant

and the definition of

nTime = Now + TimeSerial(0, 0, 10)

which after some trial and error I believe goes within a procedure.

Were exactly do these 2 items need to go ie ThisWorkbook, Module or worksheet?

I have tried all sorts of combinations, the variant statement everywhere and
the definition in each sub that uses it, but nothing seems to work.

I'm assuming that the public statement means that once it is defined it can
be used anywhere but my code triggers only one save of the file.

Help Please I've been trying to figure this out till the early hours of the
last 3 nights and I need some sleeeeeeep!

Thanks in advance

Here's the code

Module 1

Option Explicit

Public nTime As Variant
Sub The_Sub()

ActiveWorkbook.SaveCopyAs "C:\Documents and Settings\Mark\My
Documents\Work\DDE Sheet" _
& "." & Format(Date, "dd-mmm-yyyy") & "." & Format(Time, "hh-mm-ss") &
".xls"

Application.OnTime Now + TimeSerial(0, 0, 10), "The_Sub", True

End Sub
Sub StopAutoSave()
On Error Resume Next

Application.OnTime earliesttime:=nTime, _
procedure:="The_Sub", schedule:=False
End Sub

Sub wbClose()
ActiveWorkbook.Close SaveChanges:=False


End Sub

Sheet

Private Sub ToggleButton1_Click()

If Me.ToggleButton1.Value = True Then
nTime = Now + TimeSerial(0, 0, 10)
Application.OnTime earliesttime:=nTime, procedure:="The_Sub", schedule:=True
Application.OnTime Now + TimeSerial(0, 1, 5), "wbClose"

ToggleButton1.Caption = "Auto Save On"
Else


Call AutoSaveOff


ToggleButton1.Caption = "Auto Save Off"


End If


End Sub

ThisWorkBook

Option Explicit

Public nTime As Variant

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "The_Sub", , False
End Sub

The Call StopAutoSave doesn't seem to work so I have resorted to shutting
the workbook down.

 

[Chip Pearson]

Mark,

which after some trial and error I believe goes within a procedure.

Nope, it goes in the Declarations section of the module, outside and before
any procedures.

Application.OnTime Now + TimeSerial(0, 0, 10), "The_Sub", True

should be

nTime = Now + TimeSerial(0, 0, 10)
Application.OnTime nTime, "The_Sub", True

See www.cpearson.com/excel/ontime.htm for more details.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Mark Dullingham]

Thanks Chip

I found your website metion in some posts a few days ago ( you probably
noticed The_Sub bit in my code) but didn't understand the call peocedure for
Stop timer.

I've subsiquently read loads more post and help file and played around a bit
and got myself in a bit of a state.

I've just been back to your site, used the code in a standard module with
the following in the worksheet-

Private Sub ToggleButton1_Click()

If Me.ToggleButton1.Value = True Then

Call StartTimer

ToggleButton1.Caption = "Auto Save On"
Else

If Me.ToggleButton1.Value = False Then

Call StopTimer

ToggleButton1.Caption = "Auto Save Off"
End If
End If

End Sub
and Hey presto it works. Many Many Thanks (this was doing my head in)

Just on last thing, is it possible to trigger 2 OnTime actions from the
toggle button say for instance to run a sub to turn of the toggle button
after an hour using

Public Const cRunIntervalHours = 1
Public Const cToggleWhat = "The_Toggle"

Many thanks again for your reply

Mark