Start countdown when form opens and close form when complete

[Wilfred_E]

I need a VBA code to auto-start and show countdown moment form opens and closes when countdown completes. I would like other form contents unfreezed (clickable and working) while countdown is counting. I have tried the following but ain't working:

Private Sub UserForm_Activate()

Application.OnTime (Now + TimeValue("00:00:01")), "UserForm_Activate"
Question1.Label1.Caption = "3"
Question1.Repaint

Application.OnTime (Now + TimeValue("00:00:01")), "UserForm_Activate"
Question1.Label1.Caption = "2"
Question1.Repaint

Application.OnTime (Now + TimeValue("00:00:01")), "UserForm_Activate"
Question1.Label1.Caption = "1"
Question1.Repaint
Unload Me

End Sub

 

[DanteAmor]

Try this:

In userform code:

Private Sub UserForm_Terminate()
  Call CountDown_End
End Sub

Private Sub UserForm_Initialize()
  Label1.Caption = 10 'Write the starting number of the count.
  Call CountDown
End Sub

In a Module:

Sub CountDown()
  If Val(Question1.Label1.Caption) = 0 Then
    Unload Question1
    Exit Sub
  End If
  Question1.Label1.Caption = Val(Question1.Label1.Caption) - 1
  DoEvents
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown"
End Sub

Sub CountDown_End()
  On Error Resume Next
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown", , False
End Sub

 

[Wilfred_E]

Try this:

In userform code:

Private Sub UserForm_Terminate()
  Call CountDown_End
End Sub

Private Sub UserForm_Initialize()
  Label1.Caption = 10 'Write the starting number of the count.
  Call CountDown
End Sub

In a Module:

Sub CountDown()
  If Val(Question1.Label1.Caption) = 0 Then
    Unload Question1
    Exit Sub
  End If
  Question1.Label1.Caption = Val(Question1.Label1.Caption) - 1
  DoEvents
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown"
End Sub

Sub CountDown_End()
  On Error Resume Next
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown", , False
End Sub

Thank you very much DanteAmor. This works perfectly and meets my need. However, I realized it would be better the countdown appear in a time format i.e. countdown from 00:00:10 to 00:00:00. Please, is this possible to achieve? Thanks

 

[DanteAmor]

Change code for this:

Private Sub UserForm_Terminate()
  Call CountDown_End
End Sub

Private Sub UserForm_Initialize()
  Label1.Caption = "00:00:10" 'Write the starting number of the count.
  Call CountDown
End Sub

Sub CountDown()
  If Question1.Label1.Caption = "00:00:00" Then
    Unload Question1
    Exit Sub
  End If
  Question1.Label1.Caption = Format(TimeValue(Question1.Label1.Caption) - TimeValue("00:00:01"), "hh:mm:ss")
  DoEvents
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown"
End Sub

Sub CountDown_End()
  On Error Resume Next
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown", , False
End Sub

 

[Wilfred_E]

Hi DanteAmor, the edit works really great! Thank you, especially for the concern and "timely" response.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Wilfred_E]

Change code for this:

Private Sub UserForm_Terminate()
  Call CountDown_End
End Sub

Private Sub UserForm_Initialize()
  Label1.Caption = "00:00:10" 'Write the starting number of the count.
  Call CountDown
End Sub

Sub CountDown()
  If Question1.Label1.Caption = "00:00:00" Then
    Unload Question1
    Exit Sub
  End If
  Question1.Label1.Caption = Format(TimeValue(Question1.Label1.Caption) - TimeValue("00:00:01"), "hh:mm:ss")
  DoEvents
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown"
End Sub

Sub CountDown_End()
  On Error Resume Next
  Application.OnTime (Now + TimeValue("00:00:01")), "CountDown", , False
End Sub

Hi DanteAmor, I really truly appreciate your assistance yesterday. However, am afraid, I would need few things added: "I would Iike the given countdown and close code to submit the selected OptionButton's value to a specified range then save and close the workbook and close form finally. I was able to use CommandButton to submit the selected value but needs and couldn't fix the countdown snipet to do same. NB: I have 4 OptionButtons wrapped in a frame. Thank you very much.