Hi all,
I have a workbook that we use in work in work with a timer function that shuts down the workbook when the workbook hasn't been used for 15 minutes.
However, if my peers select a cell and go into edit mode then no macros run and then they go home so no one can edit it.
Is there a modification or a different VBA code that I can use that closes the workbook down without saving after 15 minutes even if a cell is in edit mode???? Pulling my hair out over this one. I've pasted my current code below.
In a module
Dim DownTime As Date
Sub SetTimer()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is where the timer is set to autoclose without saving
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
DownTime = Now + TimeValue("00:015:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=False
End Sub
Sub ShutDown()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This is where the timer is set to autoclose without saving, chage false to true to save when closing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.DisplayAlerts = False
'Application.ScreenUpdating = False
With ThisWorkbook
.Saved = False
.Close
End With
End Sub
In the workbook area
Private Sub Workbook_Open()
Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call StopTimer
Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Call StopTimer
Call SetTimer
End Sub
THANK YOU!!
I have a workbook that we use in work in work with a timer function that shuts down the workbook when the workbook hasn't been used for 15 minutes.
However, if my peers select a cell and go into edit mode then no macros run and then they go home so no one can edit it.
Is there a modification or a different VBA code that I can use that closes the workbook down without saving after 15 minutes even if a cell is in edit mode???? Pulling my hair out over this one. I've pasted my current code below.
In a module
Dim DownTime As Date
Sub SetTimer()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is where the timer is set to autoclose without saving
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
DownTime = Now + TimeValue("00:015:00")
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, _
Procedure:="ShutDown", Schedule:=False
End Sub
Sub ShutDown()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This is where the timer is set to autoclose without saving, chage false to true to save when closing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.DisplayAlerts = False
'Application.ScreenUpdating = False
With ThisWorkbook
.Saved = False
.Close
End With
End Sub
In the workbook area
Private Sub Workbook_Open()
Call SetTimer
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call StopTimer
Call SetTimer
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Call StopTimer
Call SetTimer
End Sub
THANK YOU!!