Countdown from variable value to zero

[Anneleen]

Dear,

I am looking for the VBA coding when I click on a button the countdown starts. When I again click on this button the countdown should stop.

Every second should deduct 1 from the value. I do not want to make something with minutes and seconds. It just needs to go from 100 till 0.

I am not able to find this as all of the examples are in minutes and seconds.

This is what I have up so far

Sub TOP_SPELER1()
timerlength = Cells(3, "L").Value
Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub looptimer()
Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub nexttick()
If Cells(3, "L").Value <= 0 Then
Cells(3, "L").Value = 0
Exit Sub
End If
Cells(3, "L").Value = timerlength - 1
looptimer
End Sub

Can somebody please help me?

Thank you!

 

[Marc L]

Hi !

As written, use code tags (or code icon) in your post …

​

 

[Anneleen]

Hi !

As written, use code tags (or code icon) in your post …

​

Hi,

The value is reduces with a different no then 1. there seems to be something wrong with this part of the coding
Cells(3, "L").Value = timerlength - 1

Can you please help me?

Thank you!
Anneleen

 

[vletm]

@Anneleen
This counts down every second from value You have set to cell [L3] before press the button. Compare...

Sub TOP_SPELER1()
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub looptimer()
    Application.OnTime Now + TimeValue("00:00:01"), "nexttick"
End Sub

Sub nexttick()
    If Cells(3, "L").Value <= 0 Then
        Cells(3, "L").Value = 0
        Exit Sub
    End If
    Cells(3, "L").Value = Cells(3, "L").Value - 1
    looptimer
End Sub

Questions?

 

[Marc L]

A countdown needs only two procedures :

one for button and another one for OnTime method …

​

 

[vletm]

@Marc L ... that's true, but I didn't want to change too much

 

[Anneleen]

Thanks, this indeeds did the trick.

The reason why I am working with the additional method TOP_SPELER1 is because there are 2 buttons on the screen. One for player 1 and one for player 2

I now need to add the logic when you click on button player one: the timer starts, when you again click on the player one the timer stops (same logic should appear for buttons 2)

Which brings me to my next question: how can I stop the timer?

Thank you!

 

[Marc L]

This is the logic for a button within an unique procedure :​

Sub CountDown()
                Const PR = "CountDown"
                Static Ch As Characters, DT As Date
    With Sheet1.Cells(3, 12)
        If IsError(Application.Caller) Then
               .Value = .Value - 1
            If .Value > 0 Then
                DT = Now + TimeValue("0:0:1")
                Application.OnTime DT, PR
            Else
                Ch.Text = "Start"
                 Set Ch = Nothing
            End If
        ElseIf Not Ch Is Nothing Then
            Application.OnTime DT, PR, , False
            Ch.Text = "Start"
             Set Ch = Nothing
        ElseIf IsNumeric(.Value) And .Value > 0 Then
                 DT = Now + TimeValue("0:0:1")
             Set Ch = .Parent.Shapes(Application.Caller).TextFrame.Characters
            Ch.Text = "Stop"
            Application.OnTime DT, PR
        Else
            Beep
        End If
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​