Recursive Loop -- How to Stop It?

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

"exit sub" an idea?

"LarryP" <[email protected]> schreef in bericht
news:[email protected]...
> 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
>

Tried that too without success.

"Gert-Jan" wrote:

> "exit sub" an idea?
>
> "LarryP" <[email protected]> schreef in bericht
> news:[email protected]...
> > 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
> >
>
>
>

how about another ontime set to 00

--
Don Guillett
SalesAid Software
[email protected]
"LarryP" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi LarryP
The only change i made was to declare both variables Public and it worked
fine for me - assuming the cmdbutton is on the form of course.

Geoff

"LarryP" wrote:

> Tried that too without success.
>
> "Gert-Jan" wrote:
>
> > "exit sub" an idea?
> >
> > "LarryP" <[email protected]> schreef in bericht
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

"Forest for the trees"! That did it. Thanks.

"Geoff" wrote:

> Hi LarryP
> The only change i made was to declare both variables Public and it worked
> fine for me - assuming the cmdbutton is on the form of course.
>
> Geoff
>
> "LarryP" wrote:
>
> > Tried that too without success.
> >
> > "Gert-Jan" wrote:
> >
> > > "exit sub" an idea?
> > >
> > > "LarryP" <[email protected]> schreef in bericht
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >

Hi LarryP
Pleased to help <g>
I find using Option Explicit in each module is very helpful for trapping
such errors.

Geoff

"LarryP" wrote:

> "Forest for the trees"! That did it. Thanks.
>
> "Geoff" wrote:
>
> > Hi LarryP
> > The only change i made was to declare both variables Public and it worked
> > fine for me - assuming the cmdbutton is on the form of course.
> >
> > Geoff
> >
> > "LarryP" wrote:
> >
> > > Tried that too without success.
> > >
> > > "Gert-Jan" wrote:
> > >
> > > > "exit sub" an idea?
> > > >
> > > > "LarryP" <[email protected]> schreef in bericht
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >