VBA Stopping a Timer

Hi,

out of codes,

you might tell us what are you trying to do

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 ?

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

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. 

???

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.

Chip:

It is your code .... all my searching on the internet revealed that most folks are using your code for this purpose.

I tried your suggestion and it does work as intended.

The next question - because I am not at work where I can test this code on the network ... the reason for re-opening the workbook after saving, was to show any changes that someone may have made to the workbook - so viewers on other terminals would be able to see the updates.

What syntax will replace "Workbooks.Open ActiveWorkbook.FullName" without causing the same problem to appear again ?

Thank you for your help.

Jim

Chris:

I wonder if my project might make use of another piece of your code listed on:

http://www.cpearson.com/Excel/OnTime.aspx  ?

More specifically :

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
    TimerSeconds = 1 ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
End Sub

However, I am not certain how this would be included in my project.   Thoughts ?

Jim