count down timer in text box

[jimbo_jones]

Hello All, I'm have a difficult time to make a count down timer in
text box. Basically I run a While loop, that does some testing.
Suring that while look I'd like a text box count down so that theuse
knows something is happening

bStopped = False 'Estop control
' Wait 10 sec
InitialTime = TIME
FinalTime = InitialTime + #12:00:10 AM#
While InitialTime < FinalTime And Not bStopped
TimerTime = FinalTime - InitialTime
lblOK.Text = TimerTime
'...

 

[K Dales]

There are 2 separate issues with your code:
1) The text in your label will not be updated unless you let Excel redraw it
- you can accomplish this with a DoEvents statement in your loop. Also, I
would suggest you format the time text or it will show as a decimal value.
2) There is a problem in how the loop is set up: it will not stop after 10
seconds (Consider: InitialTime is always less than FinalTime, right?). Same
issue in how you are calculating the elapsed time to display - use the
current time (Time) in the calculations instead.

Here is a modified version:
bStopped = False 'Estop control
OldText = "" ' used to see when timer text has changed (see below)
' Wait 10 sec
InitialTime = Time
FinalTime = InitialTime + #12:00:10 AM#
While Time < FinalTime And Not bStopped
TimerTime = Time - InitialTime
' above calculation gives elapsed time;
' use FinalTime - Time if you want instead a "countdown" timer
lblOK.Text = Format(TimerTime, ":ss")
' Use DoEvents to allow Excel to update the label
' but if loop runs fast, constant redraw creates flicker
' so only redraw when necessary - when text changes
If lblOK.Text <> OldText Then DoEvents
OldText = lblOK.Text ' reset OldText to track text changes on next loop
....
Wend

 

[jimbo_jones]

I've searched on this topic, but the code I've found is extremely long,
for somethins that seems very simple. Can someone tell me if the above
idea will take more than 10 lines of code? Thanks.

 

[jimbo_jones]

Thanks K Dales, I just started programming in Excel VBA and also just
found Google.Groups, which has a lot of information!