Limit the # times a macro code will run

I am putting together a quiz with a number of questions, some multiple choice, some true/false.  For each question I will have a "Retry" function and a "Next" function.  If a user get the wrong answer then I want to allow another try or perhaps 2 more tries to get the correct answer.  So i want to write the macro to so that when the user clicks on "Retry", a click will be counted.  If the count exceeds the number of allowable tries, then nothing happens and the user will be forced to click "Next" to move onto the next question leaving the one before with the incorrect answer.
jlove88Program ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you supply a sample workbook?
jlove88Program ManagerAuthor Commented:
Here you go.  Each question is on a separate sheet.  One sheet will be active at a time, so when say, Q1 is answered and the user clicks Next, sheet 2 will unhide and then sheet 1 will be hidden.
ShumsExcel & VBA ExpertCommented:
What would be end result? I mean after completing 6 questions?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jlove88Program ManagerAuthor Commented:
The end result:  The user needs to answer all the questions and score 80% or more.  If this score or greater is reached, a congratulatory message will display.  The user will be asked to enter their name. Once entered the user will be asked to click a button that will invoke a macro to send an email to an email address with a notification that the user has completed the course.

There are a  number of courses that we wish several people to complete.  This template (with different questions) will be used as a end of course assessment.  This is a temporary measure until we can get this function into an LMS.  In the meantime I need a workaround and I think I can get it done using Excel.
Martin LissOlder than dirtCommented:
See if you like this approach (only sheet1 is done).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
There are a few wats to do this.  You can involve the compter registry (or a config file) to count the number of attempts.

You could have a website that tells the macro what to do. Each attempt would be logged on a website.

You could (overkill) use a licensing tool to limit use.

Or try an alternate approach like an online survey tool....

How important is it to prevent / detect cheating?
jlove88Program ManagerAuthor Commented:
Martin, thanks.  This is headed in the direction I am looking for.  Before I accept the solution and close the question I would like to proceed with your solution but have the ability to ask another question if needed.  I expect to finalize this template in the next 8 hours so that is how long the question will remain open.
Martin LissOlder than dirtCommented:
Sure. I'll be waiting.
jlove88Program ManagerAuthor Commented:
I have copied the code to all the sheets and changes the parameters as needed.  On Sheet 2, I have entered formulas to record which indicate if the answer given is correct or not.

For the code in the last question, I need to have it check Sheet2, cell C11.  If the % is 80 or greater, then display "Congratulations you have completed the course.  Enter your name:  {whatever the persons name is}.  By click OK in the message box, I want the macro to send an email to an email address I will hard code that will basically say the John Doe has successfully completed X course.

On the other hand, if the final score is <80%, then a message saying "Sorry, you did not attain a passing score of 80%.  You will need to retake the course and the assessment".  At which point all the answers will be cleared.

I don't expect you to write this entire code but would be grateful for some general direction.

Much appreciated.
Martin LissOlder than dirtCommented:
I haven't had a chance to look at your update yet, but try this. Each sheet has this code.
Private Sub Worksheet_Activate()
' Sheet number and answer cell address
Init 1, "D9"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D9")) Is Nothing Then
    ' Sheet number, Target cell, maximum tries, and optionally "True"
    ' indicating that this is the last sheet
    EvaluateAnswer 1, Target, 3
End If

End Sub

Open in new window

jlove88Program ManagerAuthor Commented:
Thank you.  I have used the coding by sheet as a solution and added another routine to send an email with results.
Martin LissOlder than dirtCommented:
Please don't forget to close this question.
jlove88Program ManagerAuthor Commented:
The solution helped me to develop a quizzing template that can be used for multiple questions.  I got what I needed.  Thank you.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.