We help IT Professionals succeed at work.

Limit the # times a macro code will run

162 Views
Last Modified: 2017-03-08
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

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
What would be end result? I mean after completing 6 questions?
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.
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
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