Problem with Application.OnTime

[zoom38]

Good morning,

My spreadsheet uses a userform to enter amounts into a chart. Once that amount is entered into the chart, another userform shows the amount of the average (value from cell D10) and I use code I found from the net to flash that amount on the userform. It works fine if one lets the routine go to its completion. However, if one presses the OK or X on the userform before the Application.OnTime routine finishes, and then clicks to enter a new amount, the former amount will show on the userform and flash, not the updated amount. I've tried many things to correct it but nothing I've done works to correct the issue. I've searched the net but can't find any issues similar to mine. Is this something that can be corrected? I've attached a shortened version of my spreadsheet to show the issue.

thx in advance,
Gary

[p45cal]

I haven't looked closely at your code but I think you should be looking at cancelling the last .Ontime you've scheduled as soon as you press the OK or X on the userform.
You can cancel/clear a previously scheduled .Ontime if:
(a) the current time is still before the Earliest argument of .Ontime
(b) you know the exact time it was scheduled for
(c) you know the macro name that was to be called

For (b), this means that each time you create a .Ontime schedule you make a note of the exact time it's scheduled for (you could use a global variable to hold that time, set the global variable first, call the .Ontime schedule using that global variable)
For (c) I'm guessing you only call one macro so you may not have to use a variable to store that.
For (a) if the Earliest time has lapsed it probably doesn't matter any more!

The way to cancel an existing .Ontime schedule is to call the scheduling again with the same time and macro name but add the 4th argument of False to the line (it's the argument named Schedule. See https://docs.microsoft.com/en-us/office/vba/api/Excel.Application.OnTime

So when you set a schedule as you do with:

Application.OnTime Now + TimeValue("00:00:01"), "next_moment"
intead use maybe:
NextMomentTime = Now + TimeValue("00:00:01")
Application.OnTime NextMomentTime, "next_moment"
where NextMomentTime is a global variable holding the time, so when you want to clear the schedule on closing the userform, include the code:

Application.OnTime NextMomentTime, "next_moment", False
to clear that schedule.

[zoom38]

p45Cal,

Thx for the tips but I'm still having issues. No matter what I do, if I don't let it finish before pressing ok on the user form, the next time I run it, it doesn't start and the value is the former value. I set the global variable as you suggested and tried using the 'Application.OnTime NextMomentTime, "next_moment", False', but no matter where I put it, it throws an error. I find the 'OnTime' routine confusing and challenging. Unfortunately the link you provided didn't really give me any more clarity.

[p45cal]

and I use code I found from the net to flash that amount on the userformCan you point us to where on the net that is? It will help us understand the intention behind the code and whether you've incorporated it into your code in the best way.

[zoom38]

Can you point us to where on the net that is? It will help us understand the intention behind the code and whether you've incorporated it into your code in the best way.

Unfortunately I can't. I tried looking for it again but couldn't locate it. However, while searching for that, I found a way to use a loop that will prevent someone from pressing the OK or X on the userform until the flashing stops. I've decided to use that instead. That code can be found at the following link:
https://forum.ozgrid.com/forum/index.php?thread/1227114-blinking-different-color-in-label-on-userform/


(https://forum.ozgrid.com/forum/index.php?thread/1227114-blinking-different-color-in-label-on-userform/)Thx for looking into my issue.
Gary

[zoom38]

For anyone interested in doing something similar. In the end, I found another method all together using the 'Sleep' method. The code I used is as follows:

Userform Code

Private Sub UserForm_Activate()
Call FAEflash
End Sub


Private Sub UserForm_Click()
End
End Sub


Standard Module Code

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Flashing As Boolean

Sub ShowFAEUserForm()
FAEform.Show
End Sub


Sub FAEflash()
Dim i As Integer
Dim x As Long

x = 200
For i = 1 To 7
If i Mod 2 = 1 Then
FAEform.Label1.ForeColor = vbMagenta
Else
FAEform.Label1.ForeColor = vbWhite
End If
FAEform.Repaint
Sleep x
Next i
End Sub


This was much easier to work with, less confusing and much easier to modify the speed of the flashing. I found the code using 'Sleep' in the following discussion:
https://stackoverflow.com/questions/18602979/how-to-give-a-time-delay-of-less-than-one-second-in-excel-vba