Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Jayden Brown   on Oct 01 In MS Office Category.

  
Question Answered By: Guillermo Cox   on Oct 01

I found the solution to the problem in Chip Pearson's
explanation on his site:
http://www.cpearson.com/excel/OnTime.aspx

So, it's working fine now. The code which I got to run
correctly was:

' ' 'the following placed in ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False

End Sub

Private Sub Workbook_Open()
'<-some code for the opening steps of the program
StartTimer
End Sub

' ' 'the following placed in a standard module:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 'that is, 5 minutes
Public Const cRunWhat = "autosavMacro"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub StopTimer()
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Sub autosavMacro()
Application.EnableEvents = False
'<-the code for the backup save steps of the program
StartTimer
Application.EnableEvents = True
End Sub

Chip agreed that the timer must be shut off, or it would keep
running after the workbook was closed, so it would try to open it
again.
This one sets it to automatically shut off when the wookbook is
closed, but if the user wants to shut it off sooner, the "StopTimer"
sub does that.

Share: 

 

This Question has 5 more answer(s). View Complete Question Thread

 


Tagged: