VBA to Close and Save Workbook on Timer

richardtims

New Member
Joined
Jun 25, 2018
Messages
31
Hello,

I found code in this forum to close my workbook after a certain amount of time, but when I use it, after closing the workbook, it wants to re-open it. Can anyone provide code that works or help me fix this one?

Code:
[COLOR=#333333]Public RunWhen As Double[/COLOR]
[COLOR=#333333]Public Const cRunIntervalSeconds = 300 ' this is 300 seconds or 5 Minutes[/COLOR]
[COLOR=#333333]Public Const cRunWhat = "The_Sub"[/COLOR]

[COLOR=#333333]Sub Auto_Open()[/COLOR]

[COLOR=#333333]RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)[/COLOR]
[COLOR=#333333]Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _[/COLOR]
[COLOR=#333333]schedule:=True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]


[COLOR=#333333]Sub The_Sub()[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]ActiveWorkbook.Save[/COLOR]
[COLOR=#333333]ThisWorkbook.Close savechanges:=True[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]Auto_Open[/COLOR]

[COLOR=#333333]End Sub[/COLOR]
 

richardtims

New Member
Joined
Jun 25, 2018
Messages
31
After I posted this, I thought that maybe taking out the Auto_Open line would do the trick but it didn't. I looked at the article, but being somewhat new to VBA, I can't make out what lines of code I should use and where I should put it. Can you provide help with that piece? I really appreciate your help.
 

richardtims

New Member
Joined
Jun 25, 2018
Messages
31
I think I may have figured it out. I wanted to post the code here so others can use it for themselves. If there is no further reply, then we know it definitely works. Thanks for your help with this.

Code:
Public RunWhen As Double
Public Const cRunIntervalSeconds = 1800 ' this is 1800 seconds or 30 Minutes
Public Const cRunWhat = "The_Sub"


Sub Auto_Open()


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

End Sub

Sub The_Sub()


ActiveWorkbook.Save


 Call StopTimer


ThisWorkbook.Close savechanges:=True


End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub
 

Forum statistics

Threads
1,078,239
Messages
5,339,027
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top