MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A bug in my macro

Posted by Sarah on July 31, 2001 2:59 PM

I have a macro that saves and closes a workbook if nothing has been entered or no macro has been called for 5 minutes. But when the workbook closes (either by macro or by clicking the X) it reopens and closes a variable number of times before finally staying closed. If Excel is closed while the workbook is open this doesn't happen. I've posted the code below and if anyone has any insight into what's causing this, it would be greatly appreciated.


Dim lasttime As Double
Dim thistime As Double

Sub Auto_Open()
Application.OnEntry = "zerotime"
lasttime = Now
thistime = Now
End Sub

Sub Auto_Close()
Application.StatusBar = False
Application.OnEntry = ""
End Sub

Sub CounterTime()
thistime = Now - lasttime
Application.StatusBar = "Unused for " + Format(thistime, "") + ". Closes at 00.05.00"
If thistime > TimeSerial(0, 5, 0) Then
End If
Application.OnTime Now() + TimeSerial(0,0,1), "countertime"
End Sub

Sub Zerotime()
lasttime = Now()
End Sub

Posted by Ivan F Moala on July 31, 2001 7:09 PM

You need to quit the Ontime call in
your auto close routine place;

Application.OnTime Now() + TimeSerial(0, 0, 1), procedure:="countertime", schedule:=False

Befor the End sub call

Ivan If

Posted by Sarah on August 01, 2001 9:27 AM

Thanks, that worked great