Time limit restriction.

[ed]

Using Excel 2000

I am unsuccessfully trying to create a time restriction on an excel file so
that if the workbook has been open for longer than 20 minutes, any changes to
the file will be saved and then the file will automatically be closed. I
would like a message box to be shown when the file is opened, stating that
the file will automatically close in 20 minutes and then have message boxes
shown at the 10, 15 and 18 minute marks as reminders the file will close.

I have tried using the Workbook_Open() event, OnTime Method and a MsgBox but
I have very limited understanding of VB and have not been able to put these
things together in a manner that will produce any results.

Any help is appreciated.

 

[Ron]

Private Sub Workbook_Open()
Start
End Sub

+

Global TimerIndex As Integer
Global TimerArray(3) As Integer

Sub Start()
TimerArray(0) = 9
TimerArray(1) = 6
TimerArray(2) = 3
TimerArray(3) = 2
TimerIndex = 0
TimerWarning
End Sub
Sub main()
TimerWarning
End Sub
Sub TimerWarning()
For a = TimerIndex To 3
TimeLeft = TimeLeft + TimerArray(a)
Next a
MsgBox ("Application will shut down in " & TimeLeft & " minutes")
Application.OnTime Now + TimeValue("00:0" & TimerArray(TimerIndex) &
":00"), "Main"
TimerIndex = TimerIndex + 1
If TimerIndex > 3 Then
Application.Quit
End If
End Sub

Should do the trick

 

[Doug Glancy]

ed and Ron,

I was interested in Ron's idea, but one thing I noticed is that
Application.Quit will shut down Excl and won't save your workbook. So I
tinkered and came up with this:

Sub TimerWarning()
Dim TimeLeft
Static TimerIndex As Long

TimeLeft = Array("20", "10", "05", "02", "00")
MsgBox ("Workbook will close in " & CLng(TimeLeft(TimerIndex)) & " minutes")
If TimerIndex > 3 Then
ThisWorkbook.Save
ThisWorkbook.Close
End If
TimerIndex = TimerIndex + 1
Application.OnTime Now + TimeValue("00:" & TimeLeft(TimerIndex) & ":00"),
"TimerWarning"
End Sub

As Ron indicated, you have to have a Workbook_Open procedure in the
ThisWorkbook module, like this:

Private Sub Workbook_Open()
TimerWarning
End Sub

You can test this by changing it to seconds, like this:

Application.OnTime Now + TimeValue("00:00:" & TimeLeft(TimerIndex)),
"TimerWarning"

hth,

Doug

 

[Ron]

Be carefull since you fil the array with 20,10 etc you will build wait loops
with these figures as well ...

 

[Doug Glancy]

Ron,

Right you are. I think this would work instead:

Application.OnTime Now + TimeValue("00:" & TimeLeft(TimerIndex-1) -
TimeLeft(TimerIndex) & ":00")

Doug