CODEWITHSUNDEEP
excelvbauserform
Amal C Pauly
Amal C Pauly

Reputation: 49

Countdown timer in Excel UserForm doesn't work

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.

Upvotes: 0

Views: 242

Answers (1)

Storax
Storax

Reputation: 12167

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.

Upvotes: 1

Related Questions