Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to cancel the OnTime Method? Thanks NateO!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Guys.

    In the following procedure, when the user places a check in in this CheckBox, a procedure is run every 60 seconds.

    The code continues looping until the checkbox is unchecked.

    What is the code needed to cancel the OnTime method?

    The help files for the OnTime method only give an example for a static time, not a loop.

    This is the line I am using in my attempt to cancel the method.


    Application.OnTime Procedure:="RefreshDeTime", Schedule:=False



    Thanks,
    Tom




    Private Sub CheckBox1_Click()

    Do Until CheckBox1.Value = False
    Application.OnTime Now + TimeValue("00:00:60"), "RefreshDeTime"
    Sheet1.Range("D2").Value = Now
    ActiveWorkbook.save
    Loop
    Application.OnTime Procedure:="RefreshDeTime", Schedule:=False

    End Sub






    [ This Message was edited by: TsTom on 2002-04-26 15:12 ]

    [ This Message was edited by: TsTom on 2002-04-26 15:41 ]

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ts, you may want read Chip's perspective on the desirability of storing the time in a variable (bottom half).

    http://www.cpearson.com/excel/ontime.htm

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-26 15:45 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks NateO
    Works just as I needed..
    Tom




    #########################################
    Standard Module TimerRefresh
    #########################################

    Public RunWhen As Date

    Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, 5)
    Application.OnTime earliesttime:=RunWhen, procedure:="RefreshDeTime", _
    schedule:=True
    End Sub

    Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=RunWhen, procedure:="RefreshDeTime", _
    schedule:=False
    End Sub

    Sub RefreshDeTime()
    Sheet1.Range("D2").Calculate
    ActiveWorkbook.save
    StartTimer
    End Sub
    #########################################








    #########################################
    Class Module Workbook
    #########################################
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
    End Sub
    #########################################








    #########################################
    Class Module Sheet1(PSRT Tracking)
    #########################################
    Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
    RefreshDeTime
    Else
    StopTimer
    End If
    End Sub
    #########################################




    [ This Message was edited by: TsTom on 2002-04-26 15:42 ]

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Glad to hear you're up and running. Have a great weekend! Say, neat trick changing the post title, is this an option for an original poster? I'm off to the test section....

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-04-27 15:18 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •