Re: Using a time Variant with OnTime

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <MarkDullingham@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:0EF8F40C-57EE-4395-B28A-3833C2BFD4A6@xxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Using a time Variant with OnTime
    ... Option Explicit ... Public nTime As Variant ... the definition in each sub that uses it, ...
    (microsoft.public.excel.programming)
  • Re: Using a time Variant with OnTime
    ... Public nTime As Variant ... the definition in each sub that uses it, ...
    (microsoft.public.excel.programming)
  • Re: Code to delete/unlink Linked tables
    ... Public intLinkODBCTables As Variant, intLinkDB2Tables As Variant ... Public strLinkBackendDB As String, strLinkDSNname As String, strLinkLibName ... ' MsgBox "This database is in MDE format...I will delete/recreate ODBC ... Public Sub fncLinkDB2Table() ...
    (microsoft.public.access.modulesdaovba)
  • Re: Difference between Subs and Functions
    ... Robert Morley wrote: ... I didn't say anything about a Sub returning a value. ... The Variant part is the problem, not the Sub Function part. ... In Access it's sometimes necessary to declare a Sub as a Function so ...
    (microsoft.public.vb.general.discussion)
  • Re: Difference between Subs and Functions
    ... Oh, and in either VB6 or VBA, you can't convert a Function to a Sub unless ... The Variant part is the problem, not the Sub Function part. ... In Access it's sometimes necessary to declare a Sub as a Function so ...
    (microsoft.public.vb.general.discussion)