Avatar of jlove88
jlove88
Flag for United States of America asked on

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.
Microsoft OfficeMicrosoft ExcelVB ScriptVBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Can you supply a sample workbook?
jlove88

ASKER
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
Shums Faruk

What would be end result? I mean after completing 6 questions?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
jlove88

ASKER
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.
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
DrTribos

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?
jlove88

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

Sure. I'll be waiting.
jlove88

ASKER
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 Liss

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
jlove88

ASKER
Thank you.  I have used the coding by sheet as a solution and added another routine to send an email with results.
Martin Liss

Please don't forget to close this question.
jlove88

ASKER
The solution helped me to develop a quizzing template that can be used for multiple questions.  I got what I needed.  Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

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