How to limit exam attempt of a user in excel

[maxterrr]

Hi All,

I have created a exam/test in excel but am stuck in with the below issue, i want that a single user can only have three attempts to clear/pass the test, failing which the user can not take the test unless we do not reset the counter again.

It will be a great help if some one can guide me or provide me with a code, many thanks

Regards

[kasan]

Hi,
It's not fully clear how does your test works. I guess it's not a problem to limit atempts to 3 while file is opened, but after user re-open file - he can have another 3 attempts.
Probably not the best solution, but:
In this case you could create some txt file somewhere on local/network disk. Before start the test - macro should check if this file exists. If not - this is first time then user run the test and have 3 attempts. After 3 attempts txt file should be created and file shold be closed. Re-opening of the test will not give additional attempts to user. This will be possible only after txt file will be deleted.

[EssoExplJoe]

You might want to add another sheet to the exam workbook that is hidden and password protected such that only you you can open in VBA and record user's id's/names, etc. when the user enters/logs into the exam spreadsheet and then exits to record how may times they have taken the test. Of course, if the user has any reasonable excel experience, he can probably break into the hidden sheet by looking at your VBA code.

[Zer0Cool]

You could try to enforce this as others have mentioned, however no matter what approach you take I see them all being able to be circumvented.

If the user opens the file with macros disabled, if they open it holding down shift to prevent events from triggering, if the user reviews the code for any password without opening the file prior, if they save a separate copy and then over write the original, etc.

Excel isnt meant to be a secure system, neither is VBA. You can make it difficult to do more than 2-3 attempts, but if they have 5 mins to Google and really want to do it more, than they will be able to.

Without any idea of how your test actually works, it may be a better idea to consider using a platform meant to administer testing.

[maxterrr]

Thanks for the suggestions, i will try the said options, meanwhile if some thing else comes up please let me know here, Many thanks.

[maxterrr]

Hello Guys, thanks for the help you provided and it will be great if you can help me a bit for on this.

Below is the output of the test result file [from Column A to Column E - where I am storing the data/record of the test) what I need is column ‘F’ and Column ‘G’ auto populated once the results come in to the sheet (column A to Column E), the data in Column F depends on the data of a Column ‘A’ and ‘D’, column ‘A’ is employee ID and is unique field where as Column ‘D’ is technology and it can be multiple to same employee (Here in the example it is Informatica, DB2, SQL), based on employee id and technology an employee can have 3 valid attempts for each technology post which the value of attempt column increments for that user and technology and column value for column ‘G’ becomes Invalid as shown in the snapshot.

I have attached the test file with the end result as well, please let me know if any more details are required to describe the case.

Regards

[jindon]

Formula in :
F2: =COUNTIFS($A$2:$A2,A2,$D$2:$D2,D2)
G2: =PROPER(IF(F2<4,"","In")&"Valid")

Conditional format in G2:G21, Formula =G2="Invalid", Choose cell color.

[AliGW]

How will these formulae limit the attempts at the test???

[mikerickson]

The notifications will limit the testers interpretation of the results. The employee can take the test as many times as they want but rverything after attempt #3 is a 0 score.

Its an easier approach than "keep them out of the workbook after 3 attempts".

[Zer0Cool]

How will these formulae limit the attempts at the test???

I dont think hes limiting it, just keeping track of which results are valid.

However @maxterrr is there a question in that post, seems like you have created what you asked for in post #6.

[AliGW]

Ah, OK - thanks, chaps.

[maxterrr]

Yes, ZerOCool, i have created some thing simpler, but still not able to restrict the users, as of now Jindon's code will keep a tract of number of attempts and it's validity, but i need to wore more on this to find out a way to restricit user. i will keep you posted mean while if you have any other idea please post it here, many thanks.

@ Jindon, Thanks as always

[jindon]

What do you mean by "restrict user"?

I don't think it is possible to do with Excel to restrict user from exam physically.

[Zer0Cool]

however no matter what approach you take I see them all being able to be circumvented.

Quoting myself on this one, the gist hasnt changed. To clarify:

NOTHING you do in Excel will prevent someone from retaking the test if it is administered in Excel.

It may deter them, slow them down, make it impractical to take beyond the limit, etc. However anyone who really wants to circumvent any limitations you put in place can and will.

Your options are:

• Come up with a way to verify the results (like you have)
• Move your testing to a program made for testing

[LeoTaxi]

Sheet Protection, password test
VBA Protection, password test

transformation to sheet1 by Userform

Please Login or Register  to view this content.

Kind regards
Leo

[maxterrr]

Thank You All for the help, closing this thread for now.