Chip Pearson explains how to use application.ontime:
http://www.cpearson.com/excel/ontime.htm

Take particular note of the way Chip uses a public variable (runwhen) to kill
the next scheduled run.

JB2010 wrote:

Hi

I have three messages that come up on the Application Status Bar for a few
seconds each & then move on to the next one in a loop. They start when the
sheet is opened up & i want them to close when the sheet closes down. The
problem i have with my current code is that when i try to close the sheet
down it automatically reopens the sheet to carry on playing the loop of
messages! I have tried using all sorts of things that should fire macros
before i close the sheet but to no avail.

can someone tell me where i am going wrong, the PrivateSubWorkbook's just
dont seem to want to interupt my rather primative loop?

here are all the PrivateSubWorkbook's that ive use to try to control the
loop with:

-----------------------------------------------

Private Sub Workbook_Open()
Application.StatusBar = "Text in here"
Application.OnTime Now + TimeSerial(0, 0, 5), "LineB"
End Sub

Private Sub Workbook_Deactivate()
Application.StatusBar = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.StatusBar = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.StatusBar = False
End Sub

---------------------------------------
...& here is the loop of messages...
---------------------------------------

Sub LineA()
Application.StatusBar = "Text in here "
Application.OnTime Now + TimeSerial(0, 0, 5), "LineB"
End Sub

Sub LineB()
Application.StatusBar = "More text in here"
Application.OnTime Now + TimeSerial(0, 0, 5), "LineC"
End Sub

Sub LineC()
Application.StatusBar = "Even more text in here"
Application.OnTime Now + TimeSerial(0, 0, 5), "LineA"
End Sub

---------------------------------------------

as always, any help gratefully recieved, let me know if you need more info

cheers

jb

--

Dave Peterson