Limit the # times a macro code will run

jlove88
jlove88 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you supply a sample workbook?
jlove88Program Manager

Author

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.
Quiz-master.xlsm
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
What would be end result? I mean after completing 6 questions?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

jlove88Program Manager

Author

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.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
See if you like this approach (only sheet1 is done).
29007050.xlsm

Commented:
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 Manager

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sure. I'll be waiting.
jlove88Program Manager

Author

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.
29007050.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

29007050a.xlsm
jlove88Program Manager

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please don't forget to close this question.
jlove88Program Manager

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial