That's actually very tricky to do, and has all kinds of potential for circumventing it.
The problem is that when VBA macros run, they usually have "control" of Excel. they don't run in "background". that means that a normal macro that runs and monitors the time wouldn't allow the person TAKING the test to make any changes. The easiest approach would be: Use the sheet_activate event to store the current time the FIRST time the sheet is activated. Then, using the Workbook_SheetSelectionChange event, each time the user clicks in a cell, compare the current time to the starting time and once 60 minutes have passed, execute the close_macro. Some ways the user could circumvent this would be: A) save the file locally and exit, then open the file again and re-select the sheet. this would restart the time. Possible solution would be to store the time in a cell and password protect the cell. Only update the cell if it is blank. Or, store the time in an external file... as long as the VBA project is protected, the user would not know where to look for the file. B) User could disable the event macros so that it would not run the selectionchange event. Of course, comparing the time the file was closed to the time it was opened would uncover users that did this. Now, I've done something similar, but using another application. I'm not sure if I could do the same with Excel, but the technique is similar. The concept is this: Windows wscript.exe runs VBScript macros. If VBScript has the sessionID of the application (in this case Excel) it MAY be able to execute an Excel macro. So, you have the Sheet_activate event of the test create a VBScript file that issues the Excel macro to protect and close. You then have VBA submit the VBScript file as a scheduled task (using: schtasks /create) when the scheduled task runs in 60 minutes, it executes the Excel macro Once again, if the user discovers that the scheduled task is being used to stop the test, all they would have to do is delete the scheduled task. (if they have the authorization) If you'd like to try one of these approaches (or others) and need assistance, let us know!! Paul ________________________________ From: vinu <[email protected]> To: MS EXCEL AND VBA MACROS <[email protected]> Sent: Thu, April 22, 2010 1:54:55 PM Subject: $$Excel-Macros$$ Need help to complete a Excel Model.. 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. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
