MrExcel Publishing
Your One Stop for Excel Tips & Solutions

A bug in my macro


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

Hi,
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.

Thanks,
Sarah


Dim lasttime As Double
Dim thistime As Double

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

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

Sub CounterTime()
thistime = Now - lasttime
Application.StatusBar = "Unused for " + Format(thistime, "hh.mm.ss") + ". Closes at 00.05.00"
If thistime > TimeSerial(0, 5, 0) Then
ThisWorkbook.Save
Auto_Close
ThisWorkbook.Close
End
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 properly....so 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

Ivan,
Thanks, that worked great

Sarah

If