krishnaa_kumarr88
New member
- Joined
- Sep 30, 2014
- Messages
- 26
- Reaction score
- 0
- Points
- 0
I have mutliple team members using one spreadsheet and on the chance someone goes to lunch and leaves the spreadsheet open, no one else can't be accesssed.
What I need is a macro that autosaves the shared workbook, then closes it if left idle for a period of time (say 5 mins).( For your information: I would like to autosave and close the shared workbook ). I am using the following code, but the problem is every 20 s my excel is closing and restarting again automatically. Does any one knows why it is restarting ?
thanks
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.AutoFilterMode = False
Call Disable
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_Open()
UserForm1.Show
Call Tempo
MsgBox "This workbook will auto-close after 20 seconds of inactivity"
Call SetTime
End Sub
module code:
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:00:20")
Application.OnTime DownTime, "ShutDown"
End Sub
Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
Schedule:=False
End Sub
Any help would be appreciated
What I need is a macro that autosaves the shared workbook, then closes it if left idle for a period of time (say 5 mins).( For your information: I would like to autosave and close the shared workbook ). I am using the following code, but the problem is every 20 s my excel is closing and restarting again automatically. Does any one knows why it is restarting ?
thanks
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.AutoFilterMode = False
Call Disable
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target _
As Excel.Range)
Call Disable
Call SetTime
End Sub
Private Sub Workbook_Open()
UserForm1.Show
Call Tempo
MsgBox "This workbook will auto-close after 20 seconds of inactivity"
Call SetTime
End Sub
module code:
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:00:20")
Application.OnTime DownTime, "ShutDown"
End Sub
Sub ShutDown()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", _
Schedule:=False
End Sub
Any help would be appreciated