Excel timer countdown for test using Application.OnTime

This Excel tutorial shows how to make Excel timer countdown for test using Application.OnTime

You may also want to read:

Excel VBA Application.OnTime Method to schedule event with timer

Send Email in Excel VBA using CDO and OLMailItem

Excel timer countdown for test using Application.OnTime

If you are going to job interview, you may be asked to do an Excel test. I have been thinking it would be awesome to make a countdown in the worksheet to tell the interviewee how much time is left. Once the time is up, the file is protected automatically and notify the interviewer by email.

excel timer 01

 

escel timer 02

excel timer 03

 

VBA code – Excel timer countdown for test

Public Const endTime As Date = #4/5/2015 5:28:00 PM#  'mm/dd/yyyy
Public Const pwd As String = "password"

Sub my_Procedure()
    Sheets("Sheet1").Range("A1") = Format(endTime - Now(), "hh:mm:ss")
    my_onTime
End Sub

Sub my_onTime()
    If Now >= endTime Then
        dummy = MsgBox("Time's up!", vbInformation)
        Application.DisplayAlerts = False
        For Each sht In ActiveWorkbook.Worksheets
            sht.Protect Password:=pwd
        Next
        ThisWorkbook.Save
        Application.DisplayAlerts = True
        Application.Quit
    Else
        Application.OnTime Now + TimeValue("00:00:1"), "my_Procedure"
    End If
End Sub

Explanation of VBA code – Excel timer countdown for test

At the beginning of the code, define the endTime, which is the date time you want to end the test.

In the second line of code, define a password for Protect Worksheet, so that the interviewee cannot change the contents once time is up.

Inside my_Procedure, define the worksheet and Range you want to display the remaining time (the counter). You can also define how you want to display the time, you can even add days and years if your counter is not for today only.

Inside my_onTime, once the current time > endTime, all worksheets will be protected with password, and then the workbook is saved and closed.

Send Email notification when time is up – Excel timer countdown for test

Read the below article if you want to add code to send email notification to an email address once the test is finished.

Send Email in Excel VBA using CDO and OLMailItem

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff196165.aspx

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *