Show countdown on userform on form opening- no control buttons.

[Wilfred_E]

Please, may I get a VBA to display countdown on a userform i.e. countdown to zero and do nothing but show MsgBox saying Time-Over? It will start counting moment the form opens, and so, no need for start/cancel buttons. Thank you.

 

[Sahak]

Hi Wilfred,

Maybe this?

Sub Timer()
    Application.OnTime Now + TimeValue("00:00:01"), "StartCountDown"
End Sub
Sub StartCountDown()
    Dim DownFrom As Single
    DownFrom = 11
    Call Timer
    Range("A1").Value = Range("A1").Value + 1
    UserForm1.Label1.Caption = DownFrom - Range("A1").Value
    On Error Resume Next
    UserForm1.Show
    If Range("A1").Value = DownFrom Then
        Application.OnTime Now + TimeValue("00:00:01"), "StartCountDown", schedule:=False
        Range("A1") = ""
        MsgBox "Time-Over"
        Unload UserForm1
    End If
End Sub

 

[Wilfred_E]

Hi Wilfred,

Maybe this?

Sub Timer()
    Application.OnTime Now + TimeValue("00:00:01"), "StartCountDown"
End Sub
Sub StartCountDown()
    Dim DownFrom As Single
    DownFrom = 11
    Call Timer
    Range("A1").Value = Range("A1").Value + 1
    UserForm1.Label1.Caption = DownFrom - Range("A1").Value
    On Error Resume Next
    UserForm1.Show
    If Range("A1").Value = DownFrom Then
        Application.OnTime Now + TimeValue("00:00:01"), "StartCountDown", schedule:=False
        Range("A1") = ""
        MsgBox "Time-Over"
        Unload UserForm1
    End If
End Sub

Hi Sahak. Don't know what am doing wrong. I tested the code on an empty userform but the form didn't show the count down on the form on test running.

 

[Sahak]

You should have UserForm1 & Label1 on it & run macro "StartCountDown"

 

[Wilfred_E]

You should have UserForm1 & Label1 on it & run macro "StartCountDown"

Yeah, I have done these earlier. Because I was testing with a new worksheet, I forgot I have not turn on the macro-setting. I later did and it works fine. Thank you very much, Sahak.

 

[Sahak]

You are very welcome.