Share via

VBA Stopping a Timer

Anonymous
2013-06-15T14:26:24+00:00

I cannot get the timer to stop, when closing my workbook, using the following:

In ThisWorkBook:

Public Sub Workbook_Open()

      Call StartTimer

End Sub

Public Sub Workbook_Close()

    Call StopTimer

End Sub

In a routine Module:

Option Explicit

Public RunWhen As Date

Public Const cRunWhat = "AutoSave"

Sub StartTimer()

   RunWhen = Now + TimeValue("00:00:10")

   Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True

End Sub

Sub StopTimer()

   Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False

End Sub

Sub AutoSave()

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    ThisWorkbook.Save

    Call StartTimer

    Workbooks.Open ActiveWorkbook.FullName 'reopens workbook to show any changes

    Application.DisplayAlerts = True

End Sub

Might you advise where the syntax error is ?

I've been researching various Excel VBA forums as well as the internet in general. Everything I've located indicates the above syntax should work - others have posted the same issue and utilize the above coding, apparently with success. So what is going on with my project ?

You assistance is greatly appreciated !

Jim

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-06-15T17:10:53+00:00

    Hi,

    since, you are going to close the workbook

    i think that it's not necessary, the..'stop timer' (disable this code)

    just close the workbook

    write in ThisWorkbook module

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Save

    End Sub

    Save

    Close

    and re-open the workbook to save changes

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2013-06-15T22:29:27+00:00

    Jim,

    That looks like some of my code. The problem is that you are closing and then re-opening the workbook, which causes VBA to stop running and reset its global variables. Normally, you would get a message prompt asking whether to save and close the workbook before re-opening it. But you have DisplayAlerts = False, so no message is displayed and Excel takes the default action, which is to save, close and re-open the file without notifying you.

    This causes RunWhen to get initialized to 0. Then, by calculation, you set RunWhen to Now+"00:00:10". This is not the same time value that was used previously by StartTimer to initialize the timer.  Therefore,  OnTime can't find the timer to stop it. Since OnTime is an application method, it will get executed as long as Excel is open, regardless of whether the workbook that initiated the timer is open. Excel will open it if necessary. The only way to stop a timer whose start time is unknown is to stop Excel.

    If you put a debug statement at the very beginning of StartTimer,

    Debug.Print "StartTimer: " & Format(RunWhen, "hh:mm:ss")

    you'll see in the Immediate window (CTRL G to view the window) that RunWhen is 00:00:00 when StartTimer is executed. The time that was used to initialize the timer no longer exists.

    Get rid of the line

     Workbooks.Open ActiveWorkbook.FullName

    and everything will work fine.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-06-15T18:17:19+00:00

    I should have provided additional background to my issue.

    With my original code posted above ... if there is another workbook open at the same time I close my workbook, my project will re-open itself automatically when the timer "fires" at ten seconds.

    If there are no other workbooks open when I close mine, my workbook will stay closed.

    Everything I've researched on the internet indicates it is necessary to stop the timer before closing ... hence the need to use :

    Sub StopTimer()

       Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False

    End Sub

    ... which includes ...  'Schedule:=False' ... that stops the timer. 

    ???

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-06-15T15:40:33+00:00

    Thank you for your reply. At the beginning of my post, I stated:

    "I cannot get the timer to stop, when closing my workbook..."

    The following, according to everything I've read on MS, Excel Forums and elsewhere should stop the timer from running when the workbook is closed:

    Sub StopTimer()

       Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False

    End Sub

    Suggestions ?

    Was this answer helpful?

    0 comments
  5. Anonymous
    2013-06-15T15:32:30+00:00

    Hi,

    out of codes,

    you might tell us what are you trying to do

    Was this answer helpful?

    0 comments