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

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