Close file automatically

[Lee]

Is it possible to set up an Excel workbook to timeout and close automatically
after a given amount of time, say 15 minutes? Maybe via a macro that times
out and closes the workbook? Or perhaps a macro from another workbook that
can close it down?

The problem we are having in the Company is that one user will open an Excel
workbook from our network and forget to close it. Then when someone else
tries to open it and make modifications they get the "File in Use" dialog box
which says "filename.xls is locked for editing." and the only buttons
available are "Read Only", "Notify", or "Cancel".

 

[Bob Phillips]

You could use Ontime together with worksheet change event code to monitor
it.


There are 4 elements here.


Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook and
reset the timer


Option Explicit


Public nSaveWB As Date


Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub


Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


You then need to set the timer in the first place, when the workbook opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).


Option Explicit


Private Sub Workbook_Open()
SetSaveWBTimer
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Lee]

Excellent, Bob. This works great! It's exactly what I was looking for.
Thanks a million.
Regards,
Lee