Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a time Variant with OnTime
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
Variant | Excel Programming | |||
Variant | Excel Programming | |||
Ontime run at a particular time | Excel Programming | |||
OnTime method only runs 1 time | Excel Programming |