excel - macro - automatic initiation of a macro at a pre-specified "system time"

[arunjoshi]

i am working on developing a test to be administered to a candidate.

how can control the process by time. when the test-taker clicks th
"start test" button, i can record the "system time" (DateTimeStart) a
that moment. but how do i ensure that at the end of a pre-specifie
duration, that is when "system time" becomes = DateTimeStart
TestDuration, a macro is initiated automatically which will end th
test.

am a novice in macro code writing. would appreciate if you can use
beginner's language in your respnse. many thanks

 

[Bob Phillips]

Check out the OnTime method. Good description at
www.cpearson.com/excel/ontime.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steve Garman]

It depends on what exactly the test consists of, but I'll try one approach

The code below assumes that the "start test" button runs a macro called
"start"

It also assumes that the candidate is entering her answers on a sheet
called "Sheet1"

The "start" macro makes Sheet1 visible and then sets a timer for 45
minutes, at the end of which time the macro "stoppem" will run.

The stoppem macro makes the sheet invisible and saves the workbook,
though currently it will ask the candidate's permission to save it.

Lots more security will be needed, like disabling the start button once
it has been run but the timer function should work OK

'''''''''''''''''''''''''''''''
Dim dateTimeStart As Date

Sub start()
Dim testDuration As Date
testDuration = TimeValue("00:45:00")
Sheets("Sheet1").Visible = True
dateTimeStart = Now
Application.OnTime _
dateTimeStart + testDuration, "stoppem"
End Sub

Sub stoppem()
With Sheets("Sheet1")
.Visible = xlVeryHidden
.Range("Z99").Value = dateTimeStart
End With
ThisWorkbook.Save
End Sub
'''''''''''''''''''''''''''''''''''''''''

 

[Rob van Gelder]

I have an example on my website
"Schedule a macro to run"