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.

VBA code – Excel timer countdown for test

Public Const endTime As Date = #4/5/2015 5:28:00 PM#
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. Test carefully whether your local setting will result in dd/mm or mm/dd. (you can add “dd hh:mm:ss”in the Format below to 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.

How to use – Excel timer countdown for test

If you are familiar with VBA, you should be able to use my code above. If not, I will explain more in this part.

First, download the workbook that contains the code. Excel Test

Now here are two sheets, by default I make the time to display in Sheet1 Range A1.

If you have any test questions prepared, move them this workbook.

Press ALT+F11, change #4/5/2015 5:28:00 PM# and “password”

Public Const endTime As Date = #4/5/2015 5:28:00 PM#

Public Const pwd As String = “password”

Click F5 to run.

Now let the interviewee do the test, the workbook will be automatically save, lock and close when time is up.

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

 

Excel timer countdown for test using Application.OnTime