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" wrote in
message ...
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, _
procedu="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, procedu="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.