Automatically Closing a worksheet after 10 mins

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hi, I have a spreadsheet that a number of people need
access to. Problems is every now and then someone opens
it and goes to a meeting or forgets to close it for some
other reason. Is there any tidy way I can wait for 10
mins of inactivity then automatically save and close a
spreadsheet ?
 
Neil,

You can set the OnTime method when you open the workbook.

This code goes in the ThisWorkbook code module

Private Sub Workbook_Open()
Application.OnTime Now + TimeSerial(0, 10, 0), "CloseWB"
End Sub

This is the code that closes the workbook, and goes in a standard code
module

Public Sub CloseWB()

With ThisWorkbook
.Save
.Close
End With

End Sub

Big problem here is that if a user updates the spreadsheet it will still
close after 10 minutes. Therefore you need to trap changes and reset the
OnTime method. This can be done using worksheet event code. This code goes
into the worksheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
Application.OnTime Now + TimeSerial(0, 1, 0), "CloseWB"
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top