View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Gert-Jan Gert-Jan is offline
external usenet poster
 
Posts: 49
Default Recursive Loop -- How to Stop It?

"exit sub" an idea?

"LarryP" schreef in bericht
...
My question involves an Excel input file where people are supposed to
enter
certain data ONLY on Thursday thru Sunday.
(1) On Save, ThisWorkbook code checks the day of the week, and if it's
outside that window, opens UserForm1 with a reminder message. This works.
(2) When the form activates, its code kicks off macro Flash1, which is
intentionally recursive, switching the reminder text between black and red
once per second to get the user's attention. This works too.
(4) the form's <OK button sets a global boolean to True and hides the
form. The boolean, in turn, SHOULD affect the next loop of the Flash1,
causing it to terminate, but it doesn't -- it keeps on running in the
background. (I also tried unloading the form rather than just hiding it,
but
no good either.)

Can anybody tell me what I'm doing wrong? I've done similar things with
Access's Timer Event, but Excel's Application.OnTime apparently isn't
quite
equivalent. (Full code follows.)

<<<ThisWorkbook Code
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
QuitNow = False
If Weekday(Date) < 4 Then UserForm1.Show
End Sub

<<<UserForm1 Code
Private Sub UserForm_Activate()
UserForm1.Label1.ForeColor = vbBlack
NTime = Now + TimeValue("00:00:01")
Application.OnTime NTime, "Flash1" 'runs Flash1 the FIRST time, after
which it runs itself recursively
End Sub

Private Sub cmdOK_Click()
QuitNow = True 'seems like this should stop the Flash1 recursive loop,
but
it doesn't.
UserForm1.Hide
End Sub

<<<MACRO CODE

Dim NTime As Date, QuitNow As Boolean 'Global variables.
Sub Flash1()
If QuitNow = True Then GoTo StopNow
NTime = Now + TimeValue("00:00:01")
If UserForm1.Label1.ForeColor = vbBlack Then
UserForm1.Label1.ForeColor = vbRed
Else
UserForm1.Label1.ForeColor = vbBlack
End If
Application.OnTime NTime, "Flash1"
StopNow:
End Sub