Give me suggestion for the below - (VBA code to complete excel model)

[vinu]

Hello Friends,


I build a model for Excel Test, Its having 3 sheets.


sheet1-- Instructions sheet2-- Test Question sheet3--- Data for the Test question.


Once if a candidate click on the Hyper link (which is in sheet1 - this will take to test question sheet i.e., sheet2), It should start time to count and should be closed in exact 60 min. Once 60 min over it should automatically protect the workbook by giving any password and should close.


Could you please guide me how do this.(give me code if its required)


Any suggestions are appreciated!


Regards,

Vinu.

 

[vinu]

Hello...


Just created the user form.It shows"This test will close in 15 min".

But I dont know how to activate this, how to track the time. Which means It should pop up at 45min(total time is 60 min), once he started test.


Please help me..


Regards,

vinu.

 

[Hui]

Do you want total time = 60 mins or total time ofn Questionaire sheet 60 mins

That is if a guy goes back to the Instructions sheet, does the clock stop?

 

[vinu]

As I said in the first post, once he clik on hyperlink(i.e., in 1st sheet), it will count till 60 Minutes, and before 15 min it shud remind him. after 60 min it should protect and save it. Then he cant open tat as its protected.

 

[vinu]

Hi


I got the code but could you pls edit the same as per my requirement


http://www.atlaspm.com/faqxlselfclose.html


Regards,

Vinu.

 

[Hui]

Vinu


Add the following code to a Worksheet Module for the sheet where the questions are

This way when the user open that sheet for the first time the timer will start.

[pre]

Private Sub Worksheet_Activate()

MsgBox "This workbook will close itself after 1 Hour.", 48, "Time Reminder"

Application.OnTime Now + TimeSerial(0, 50, 0), "Warning" 'Show Warning after 50 Mins
Application.OnTime Now + TimeSerial(1, 0, 0), "CloseMe" 'Run CloseMe after 1 Hr

End Sub

and add the following to a general module


Private Sub CloseMe()

Direct = "C:Questions" 'Where do you want the questions saved
FName = "Questions " 'File name will have the date and time appended to it ie Question 100401 1605.xlsm
FName = FName + Application.Text(Now(), "yymmdd hhmm")
FName = FName + ".xlsm"

ActiveWorkbook.SaveAs Filename:=Direct + "" + FName, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

ThisWorkbook.Close True

End Sub

Private Sub Warning()
Msg = "10 Minutes Remaining"
Title = "10 Minutes Remaining"
MsgBox Msg, vbOKOnly, Title
End Sub

[/pre]
You can adjsut the times for the Warning and Closure by adjusting the times in the 2 lines

'Application.OnTime Now + TimeSerial(0, 50, 0), "Warning"

Application.OnTime Now + TimeSerial(1, 0, 0), "CloseMe"'


Make sure you have a directory setup where you want to save your answers


Save the file with a password before giving it to the users and the password will be carried on automatically as the file is saved.

 

[vinu]

Hi Hui,


Thanks a lot...!!


Vinu.