Countdown timer in Excel UserForm doesn't work

Question

I have two identical workbooks, each containing a UserForm. The UserForms launch on opening the workbooks. Each UserForm contains a countdown timer, the code of which is given below. I need the UserForms such that when I click a Submit button on UserForm1, the WorkBook1 (and UserForm1) should close and WorkBook2 (and UserForm2) should open.

This is the code in SubmitButton_Click():

Workbooks.Open "WorkBook2.xlsm"
ThisWorkbook.Close True

I have set the ShowModal property of the UserForms to False, so they are modeless.

Code for countdown timer:

Private Sub UserForm_Activate()

Dim T, E, M As Double, S As Double
Const AllowedTime As Double = 50 ' Countdown time in minutes

T = Timer
Do
    E = CDbl(Time) * 24 * 60 * 60 - T
    M = AllowedTime - 1 - Int(E / 60)
    S = 59 - Round((E / 60 - Int(E / 60)) * 60, 0)

    TimeLabel.Caption = Format(CStr(M), "00") & ":" & Format(CStr(S), "00")

    DoEvents
Loop Until (Timer - T) / 60 >= AllowedTime

Unload UserForm1

End Sub

The timer works perfectly in UserForm1. Whenever I click the Submit button, WorkBook2 opens up and shows the UserForm2, but the timer doesn't run (it just stands still at 50:00). Also, I noticed that when I manually close the UserForm1, the VBA editor shows that the form is still running. I have to manually press the Reset button every time.

The code for timer was taken from an Excel forum. I am fairly new to Excel and VBA, so I don't know exactly how it works. Anyway, I found that whenever the form is closed/unloaded, the code is paused at DoEvents. The form stops completely only when I press the Reset button in the VBA editor.

Why does this happen? Is there a way to fix the timer? Thank you.

At first glance I'd say Unload UserForm1 in the activate event of userform1 will give you trouble. — Storax
This is happening because when you open userform2 in modal, the code execution stops and will continue only when the userform2 closes. Open the userform2 in vbmodeless and you will not face this problem userform2 .show vbmodeless — Siddharth Rout
@Storax I tried removing Unload UserForm1, still doesn't work. — Amal C Pauly

Storax Storax · Accepted Answer · 2020-09-05T09:07:48

It seems that the OP wants to close Userform1 after a certain amount of time. As the post does not really tell how userform1 is opened I suggest to test the following code: This code in a normal module

Option Explicit
Dim frm As UserForm1
Sub KillUserForm()
    Unload frm
End Sub
Sub Test()

    Set frm = New UserForm1
    Load frm
    frm.Show
    
End Sub

In the code of userform you now replace the line Unload UserForm1 with KillUserForm.

Countdown timer in Excel UserForm doesn't work

1 Answers