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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a time Variant with OnTime
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a time Variant with OnTime
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 "Chip Pearson" wrote: 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. |
Reply |
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 |