Countdown timer in Userform

[m_in_spain]

I am going round and round in circles trying to get a countdown of minutes to display in a userform.
In the excel worksheet I have a calculated value, as a number of minutes, in Range("Redu01")
When the countdown starts i want Label26.caption to display that value of minutes only, and reduce down to zero, displaying the minutes counting down in Label26
There is a green bar, reducing as time passes, which i turn red when the value displayed is less than 5.
as the value is usually more than 30 minutes, i have a line to reduce the time to 6 minutes between the '=== marks for test purposes

n = Range("Redu01")
'=== for test
If n > 6 Then n = 6
'===
UserForm1.Label26.Caption = n
For i = 1 To n
Application.Wait (Now + #12:01:00 AM#)
DoEvents
UserForm1.Label26.Caption = Format(DateAdd("m", -1, UserForm1.Label26.Caption), n)
Label28.Width = 66 - 66 * i / n
If UserForm1.Label26.Caption < 5 Then
Label28.BackColor = vbRed
End If
Next i

I am hoping someone far cleverer than I can tell me where I am going wrong.. and how to correct it.

Thanks in advance for any help

 

[Rhodie72]

I'm guessing you are wanting to create a clock based on system time. It's not impossible but do ber in mind that as long as the code is running, excel will lock you out until code completes execution.
The way to do this without locking you out is to store the current time in an array as variant. Set MyTimer(A,B,C) as the array. Set A as the initial time time value is captured, B is the current time, c is the target time.
Now each time something changes somewhere in your application it can reference these values to compare elements of the array.

To understand timekeeping in Excel, you need to understand how the time value is recorded. Does this help at all?

 

[m_in_spain]

I'm guessing you are wanting to create a clock based on system time. It's not impossible but do ber in mind that as long as the code is running, excel will lock you out until code completes execution.
The way to do this without locking you out is to store the current time in an array as variant. Set MyTimer(A,B,C) as the array. Set A as the initial time time value is captured, B is the current time, c is the target time.
Now each time something changes somewhere in your application it can reference these values to compare elements of the array.

To understand timekeeping in Excel, you need to understand how the time value is recorded. Does this help at all?

Hi, Thanks for the answer. Its not quite what I am trying to do. The excel sheet calculates a number of minutes before the next part of the process continues. E.g. it will say 35 minutes before the process continues. The number of minutes varies, depending on the ingredients. I am trying to get that number of minutes displayed on the userform, and to reduce in one minute divisions, show the ne lower number, 34,33,32 etc down to zero, when my operator will progress to the next stage of his progress. I can get the number (using my testing shortcut) ^ to display, then instead of 5 it goes to 11:59 or in one instance -6.
As i said i am going round in ever decreasing circles!

 

[m_in_spain]

Looks like i have solved this. I was trying to format this as time, when i just really wanted the number to display and reduce.

'====timer
n = Range("Redu01")
'=== for test
If n > 6 Then n = 6
'===
UserForm1.Label26.Caption = n
For i = 1 To n
Application.Wait (Now + TimeValue("00:01:00"))
DoEvents
n = n - 1
Label26.Caption = n
Label28.Width = 66 - 66 * i / n
If UserForm1.Label26.Caption < 5 Then
Label28.BackColor = vbRed
End If
Next i

Now i am going to try to understand what Rhodie72 wrote above about putting the numbers into an array so it does not lock up excel
Thanks for that