| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 31492 | 2003-03-23 20:41:00 | Close Workbook on timeout | nw3mrgc (3435) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 130368 | 2003-03-23 20:41:00 | Hi Is there a command to automatically close an Excel document after a set timeout time? We are 3 to use an Excel document, and sometimes one of the users forget to close the document and the other users cannot access it (can only read only). A command/macro could check if the document has been idle for say 10 mins, after which it closes the document automatically so that other users can have access to it. |
nw3mrgc (3435) | ||
| 130369 | 2003-03-24 04:53:00 | This can be done by incorporating VB code into the workbook. The code starts a timer when the workbook is opened, and "watches" for Application Events (ie defined user input) which resets the timer to start again. If there are no designated Application Events within the required time period, the Workbook can be saved and closed by the VB code. With the VB code in place, all the above is transparent to users. If this is suitable, code can be posted to copy into the VB editor of the required workbook. |
Russell D (18) | ||
| 130370 | 2003-03-24 09:15:00 | Thanks for the reply russell. I am having some difficulties logging in press f1. What you proposed is exactly what I was after. Could you please post the code here. I'll copy and paste it in the excel sheet. Thanks a lot. | nw3mrgc1 (3436) | ||
| 130371 | 2003-03-24 09:32:00 | Copy this into a module Option Explicit Dim Lasttime As Double Dim Thistime As Double Sub Auto_Open() Application.OnEntry = "zerotime" Lasttime = Now Thistime = Now CounterTime End Sub Sub Auto_Close() Application.StatusBar = False Application.OnEntry = "" Application.OnTime Now() + TimeSerial(0, 5, 0), procedure:="countertime", schedule:=False End Sub Sub CounterTime() Thistime = Now - Lasttime Application.StatusBar = "Unused for " + Format(Thistime, "hh.mm.ss") + ". Closes in 00.05.00" If Thistime > TimeSerial(0, 5, 0) Then ThisWorkbook.Close True End End If Application.OnTime Now() + TimeSerial(0, 0, 1), "countertime" End Sub Sub Zerotime() Lasttime = Now() End Sub |
seacrest (2893) | ||
| 130372 | 2003-03-24 09:50:00 | Thanks seacrest. It works perfectly. | nw3mrgc1 (3436) | ||
| 130373 | 2003-03-24 09:54:00 | It does not seem to like it when I close the file manually by closing the file or excel. Looks like I need to leave the code to close the file automatically. Any suggestions? | nw3mrgc1 (3436) | ||
| 130374 | 2003-03-24 09:55:00 | Sorry left out some code Copy this into the workbook Module Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Zerotime End Sub |
seacrest (2893) | ||
| 130375 | 2003-03-24 20:06:00 | Thanks for the codes seacrest. I can't close the file without some run tmie error 1004. Method 'ontime' of object '_application' failed... when I try to close the sheet, or excel. Any help there. Thanks. | nw3mrgc1 (3436) | ||
| 130376 | 2003-03-25 02:15:00 | If you are still having trouble with manual saves, try this alternative code. Option Explicit 'Code in ThisWorkbook Dim aTime As Date Dim Interval As String Private Sub Workbook_Open() Interval = "00:05:00" aTime = Now + TimeValue(Interval) Application.OnTime aTime, Procedure:="closeFile" End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range) Application.OnTime aTime, Procedure:="closeFile", Schedule:=False aTime = Now + TimeValue(Interval) Application.OnTime aTime, Procedure:="closeFile" End Sub Option Explicit 'Code in Module Sub closeFile() Dim t As Date Beep t = Now Application.StatusBar = "Closing File" Do While (Now - t) * 24 * 60 * 60 < 5: Loop Application.StatusBar = False ThisWorkbook.Close savechanges:=True End Sub HTH |
Russell D (18) | ||
| 130377 | 2003-03-25 08:24:00 | Thanks Russell. I am trying it now. | nw3mrgc1 (3436) | ||
| 1 2 | |||||