98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Close and Saved after 5 mins of inactivity code not saving
Close and Saved after 5 mins of inactivity code not saving
Resolved · Low Priority · Version 2010
Mon 1 Dec 2014, 15:42Delegate Emma said...
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
Tue 2 Dec 2014, 10:03Trainer Jens said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Trace Dependents / Precedents without the blue arrowsRather than using the toolbar you can press CTRL+] which is the equivelent of trace dependants and CTRL+[ for precendants. Both of these ways though will not show the blue arrows but jump to the cell containing the formula. |
