Time to go home…

I do a lot of work in spreadsheets and some cannot be left open on my PC as that’d make them locked for the morning report refresh. After a bunch of times having to buy cakes for so delaying the reports, I put something in place to stop it. It also had the very nice side effect of telling me to go home.

The first thing to do is make sure you have a personal workbook. Then you need to add code in two places.

This code goes in the ThisWorkbook object:

Private Sub Workbook_Open()
'Feel free to change the time - I just like to be a 9 to 5er  
Application.OnTime TimeValue("17:15:00"), Procedure:="gohome"
End Sub

Then, this code goes in one of your modules:

Sub gohome()
 
Dim WB As Workbook
For Each WB In Workbooks
'Change pathfile to your preferred destination.  Doesn't just Save as you may not want the changes confirmed without your say so
pathfile = "C:UsersslockeDesktop"
namefile = WB.Name
If Not WB.Name = ThisWorkbook.Name Then
 
WB.SaveAs Filename:=pathfile & namefile, addtomru:=True
WB.Close savechanges:=False
End If
Next WB
'Change message, or delete line 
MsgBox("I just had to close your spreadsheets. You'd better go meet Oz since you're late! ")

End Sub

The result is a macro that will save to your preferred location any spreadsheet still open at the time you think you should be going home. It saves to my desktop by default so that I don’t make any changes accidentally.

Search