Problem with resetting countdown timer

[P5C768]

Hi, I have some coding that navigates through a sheet and move data from a combobox to the sheet. However, I want to add a countdown timer on an existing userform and only allow users a given amount of time to add data. Here is the timer:

Public Const nCount As Long = 60
Public nTime As Double
Public Sub RunTimer()
 
    If nTime > 0 Then
        nTime = nTime - 1
        UserForm1.lblCountDown.Caption = Format(TimeSerial(0, 0, nTime), "[mm]:ss")
        Application.OnTime Now + TimeSerial(0, 0, 1), "RunTimer"
    Else
        UserForm1.lblCountDown.ForeColor = vbRed
    End If
End Sub

And the timer is referenced here:

Private Sub CommandButton1_Click()
 
        nTime = ncount
        Call RunTimer
 
ActiveCell.Value = Me.txt1.Value & "-" & Me.cbo1.Value
 
    If ActiveCell.Row Mod 2 = 1 Then
        If ActiveCell.Column = 3 Then
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(0, -1).Select
        End If
    Else
        If ActiveCell.Column = 14 Then
            ActiveCell.Offset(1, 0).Select
        Else
            ActiveCell.Offset(0, 1).Select
        End If
    End If
 
Me.cbo1.Value = vbNullString
Me.txt2.Value = vbNullString
Me.txt3.Value = vbNullString
Me.txt4.Value = vbNullString
End Sub

The code works for the first selection but than begins subtracting an additional second each time the command button is used (i.e. the first time command button is clicked, it starts counting down by 2's, then 3's etc)

I think I just need some coding to reset the countdown each time. Any help? Thanks!

 

[bensonsearch]

i think the problem would be with ncount... where or what is ncount? i cant see it in this code

 

[P5C768]

nCount is just a constant set to 60, which in this case represents the seconds being counted down from.

 

[P5C768]

I completely removed the reference to ncount. Now, when the command button is clicked it sets ntime = 60. Still having the same problem. Counting down by 2's, then 3's. This is what I would expect to happen if I added a counting loop and added 1 to the timeserial fuction after each loop.

I've also noticed that if I pick a time over 60 seconds, the timer displays as 12:30 and counts down to 0 and then starts over at 59, but the minutes portion always displays "12." The seconds are correct but why doesn't it show as 01:30 for 90 seconds on the clock?

 

[bensonsearch]

maybe declare ntime as a time? i dont have excel handy but there is a date variable type maybe a time one to?

with the 90 secs not displaying as 1:30 its a matter of it doesnt know its the same thing. maybe some function/formula that tests if ntime is greater than 60 if so divide by 60. eg 90 is greater than 60 therefore ntime = 90/60 (1.5)

 

[P5C768]

There is no "time" variable in excel, save for "date."

I have figured out that if I create a procedure to stop the timer and then use my existing code to start it again, it works just fine. However, if I try to call the procedure in the same one where my existing code is, it doesn't work. For example, if I run this first, before starting the timer, it works:

Public Sub ResetTimer()
    nTime = 0
End Sub

However, this does not work:

Call ResetTimer

if it's included in the same event as the rest of my code.

Basically, I could solve my problem by having two buttons on the form that start and stop the timer, but I would rather have just one button that stops, resets and starts the timer again when clicked.

 

[bensonsearch]

if ntime is declared above all code outside of any sub then this will work.

 

[P5C768]

It is declared outside of the sub. Still counts down by 1 then 2,3,4 etc.

Public Const nCount As Long = 60
Public nTime As Double

The on click event of my button should set nTime=nCount

 

[bensonsearch]

maybe try using a loop under runtimer sub instead of a if statement refering to same sub (maybe this is causing the additions)

something like
ntime = 60
do until ntime = 0
ntime = ntime - 1
UserForm1.lblCountDown.Caption = Format(TimeSerial(0, 0, nTime), "[mm]:ss")

loop