stop application on time

[p4nny]

Hi

My code autosaves a workbook every 10mins. Problem is, the workbook keeps opening once it is closed.

Here is my code:

Workbook
Application.OnTime Now + TimeValue("00:10:00"), "savethis"

Module1
Sub savethis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:10:00"), "savethis"
End sub

Please could you help me cancel this on or before event workbook close.

I'm new to vba.

Cheers

 

[mole999]

where you got the code from should have had a stop event availabe

what you need to do is something like
Sub StopTimer()
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

i.e cancel the schedule

 

[joeu2004]

My code autosaves a workbook every 10mins. Problem is, the workbook keeps opening once it is closed.

You've done very well for someone who is "new to vba".

Make the following changes, indicated in red.

Module1

Public saveTime As Double    ' I avoid type Date

Sub savethis()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
startSaveTimer
End Sub

Sub startSaveTimer()
saveTime = Now + TimeValue("00:10:00")    ' I prefer TimeSerial(0,10,0)
Application.OnTime saveTime, "savethis"
End sub

Sub stopSaveTimer()
On Error Resume Next
Application.OnTime saveTime, "savethis",, False
End Sub

Workbook

Private Sub Workbook_Open()
startSaveTimer
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
stopSaveTimer
End Sub

Caveat: saveTime is reset to zero when VBA is "reset". This can occur when you click on Run / Reset. In that case, be sure to execute stopSaveTimer manually before clicking on Reset. However, there are other actions that cause VBA to reset global variables automatically and silently; for example, adding a global variable in Module1. AFAIK, the only reliable design is to write saveTime to a worksheet and to read saveTime from it. Usually, that is more trouble than it's worth. It's up to you.

 

[p4nny]

Very kind of you- works a treat!

 

[Jmurik]

Hi, I have a similar problem, mabe you can help me too?

I was trying to adapt your advice into my code but it didn't work out (workbook keeps reopening).

Would be very grateful for help.

[/COLOR][COLOR=#333333]Sub Update_every_30_sec()[/COLOR]
Sub Update_every_30_sec ()
    Application.Calculation = xlAutomatic
    Application.Calculation = xlManual
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:10"), "Update_every_30_sec"
End Sub


Sub Auto_Open()
    Application.Calculation = xlAutomatic
    Application.Calculation = xlManual
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:10"), "Update_every_30_sec"
End Sub


Sub Workbook_BeforeClose(Cancel As Boolean)
    StopMacro = True
    Application.OnTime SchTime, "Update_every_30_sec", , False
    Application.OnTime SchTime, "Auto_open", , False 
End Sub[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]