Mr. Excel experts!
How do I display a message for x time units then have the message disappear? Other code I've seen requires the user to OK the form. Basically, I have code to that saves/closes after xx time units. I want to notify the users at the beginning that the workbook will close after XX time of inactivity. Here's the code. How do I work around needing a response? Using this code if the user never OK's it, the timer never starts. Make sense?
Thanks.
This workbook
Private Sub Workbook_Open()
Call SetTime
MsgBox "This workbook will auto-close after 5 minutes of inactivity"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
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
Module 1
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:05:00")
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
How do I display a message for x time units then have the message disappear? Other code I've seen requires the user to OK the form. Basically, I have code to that saves/closes after xx time units. I want to notify the users at the beginning that the workbook will close after XX time of inactivity. Here's the code. How do I work around needing a response? Using this code if the user never OK's it, the timer never starts. Make sense?
Thanks.
This workbook
Private Sub Workbook_Open()
Call SetTime
MsgBox "This workbook will auto-close after 5 minutes of inactivity"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
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
Module 1
Dim DownTime As Date
Sub SetTime()
DownTime = Now + TimeValue("00:05:00")
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