Emma has attended:
Excel VBA Introduction course
Close and Saved after 5 mins of inactivity code not saving
Hi,
The below code is supposed to save and close an excel spread sheet after 5 mins of inactivity,however it just closes and doesnt save. Can anyone tell me how to fix this?
Many thanks
This Workbook:
Private Sub Workbook_Open()
Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call StopTimer
Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Call StopTimer
Call SetTimer
End Sub
Moduel 1:
Dim DownTime As Date
Sub SetTimer()
DownTime = Now + TimeValue("00:05:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown()", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown()", Schedule:=False
End Sub
Sub ShutDown()
Application.DisplayAlerts = False
With ThisWorkbook
.Saved = True
.Close
End With
End Sub
RE: Close and Saved after 5 mins of inactivity code not saving
Hi Emma,
Thank you for the forum question.
Almost everything in your code was correct. I have changed a little bit in the module (see code below). When you call a macro (Shutdown) it is without the brackets I also changed the Shutdown() macro a little bit.
It is working for me and I hope it will work for you. If not please let me know.
Dim DownTime As Date
Sub SetTimer()
DownTime = Now + TimeValue("00:05:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=False
End Sub
Sub ShutDown()
Application.DisplayAlerts = False
ThisWorkbook.Close savechanges:=True
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector