This Excel tutorial shows how to make Excel timer countdown for test using Application.OnTime
You may also want to read:
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# '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.