Keeping score on a quiz

[RodneyW]

Good morning. I've set up a quiz, screen shot below, testing knowledge of airport codes. When started, cells D18 and E18 are empty. The user enters their answer in D18. The correct answer appears in E18. If there's a match, both cells are green. If wrong, both cells are yellow. When a user double clicks NEXT, it moves to the next question, clearing out D18 and E18. All works perfectly. If it helps, here's a reference the post as to how I got to where I'm at: Setting up a test for students to get the right answer

Now what I need is a way to do three calculations.

1. Count how many answers the user has submitted. This number will go in J18.
2. Count how many answers are correct. This number will go in cell K18.
3. A simple mathematical formula will populate L18.

Lastly, and I maybe should put this in a different post, but it may be structurally important to ask it now, if someone wants to restart the test and clear scores, clicking Clear Results in N18 should take J18 and K18 back to 0.

Thanks in advance.

 

[dreid1011]

Do you have some VBA already that populates E18 when a value is entered into D18 or is it a formula? Whichever it is, could you post it? If you already have VBA code, then it shouldn't be hard to add functionality for J18:K18.

 

[RodneyW]

Do you have some VBA already that populates E18 when a value is entered into D18 or is it a formula? Whichever it is, could you post it? If you already have VBA code, then it shouldn't be hard to add functionality for J18:K18.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Long

Const DataAddress As String = "D7:E128" '<- Note this includes BOTH columns of data

If Target.Address(0, 0) = "H18" Then
Cancel = True
Randomize
n = 1 + Int(Rnd() * Sheets("Data").Range(DataAddress).Rows.Count)
Range("C18:E18").Formula = Array("=INDEX(Data!" & DataAddress & "," & n & ",1)", _
"", _
"=IF(D18="""","""",INDEX(Data!" & DataAddress & "," & n & ",2))")
End If
End Sub

 

[dreid1011]

Try this VBA code for the score keeping:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitNow

Application.EnableEvents = False

If Not Intersect(Target, Range("D18")) Is Nothing Then
    If Range("D18") <> "" Then
        Range("J18") = Range("J18") + 1
        If Range("D18") = Range("E18") Then
            Range("K18") = Range("K18") + 1
        End If
    End If
End If

ExitNow:
Application.EnableEvents = True

End Sub

And this in L18 if you don't already have a formula there for the % correct:

=IF(J18+K18=0,"",K18/J18)

Is "Clear Results" a button, or do you want it to behave like "Next" when double clicked?

 

[RodneyW]

Try this VBA code for the score keeping:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ExitNow

Application.EnableEvents = False

If Not Intersect(Target, Range("D18")) Is Nothing Then
    If Range("D18") <> "" Then
        Range("J18") = Range("J18") + 1
        If Range("D18") = Range("E18") Then
            Range("K18") = Range("K18") + 1
        End If
    End If
End If

ExitNow:
Application.EnableEvents = True

End Sub

And this in L18 if you don't already have a formula there for the % correct:

=IF(J18+K18=0,"",K18/J18)

Is "Clear Results" a button, or do you want it to behave like "Next" when double clicked?

WOW, thanks! I think this is working. I want to test enter a few more answers but so far this looks like it's going to work. Regarding the "Clear Results", the concept is that someone may answer twenty or so questions and let's say they have a score of 82% which they aren't happy with. They go study, come back and instead of starting with a score of 82%, they click "Clear Results" and cell J18 and K18 both revert to 0. I realize the end user could simply change those values to 0, but a "Clear Results" function may be more intuitive.

 

[dreid1011]

WOW, thanks! I think this is working. I want to test enter a few more answers but so far this looks like it's going to work. Regarding the "Clear Results", the concept is that someone may answer twenty or so questions and let's say they have a score of 82% which they aren't happy with. They go study, come back and instead of starting with a score of 82%, they click "Clear Results" and cell J18 and K18 both revert to 0. I realize the end user could simply change those values to 0, but a "Clear Results" function may be more intuitive.

Right, but do you want "Clear Results" to be a button, or the same as the "Next" functionality (double click)? These two options require different code to make work.

 

[RodneyW]

Right, but do you want "Clear Results" to be a button, or the same as the "Next" functionality (double click)? These two options require different code to make woWHich is easler

Which is easier? Maybe just a simple button they only click once

 

[dreid1011]

Which is easier? Maybe just a simple button they only click once

Okay, if you do a button, you have to add the button to the sheet and assign the code to it. If you do a double click, we can just add the code to the current double click code, but I will do both and you can choose which you want to use.

 

[dreid1011]

Alright, adjusted your "before double click" code to work for Clear Results as well as Next if you want it that way. If not, keep your current code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Long

Const DataAddress As String = "D7:E128" '<- Note this includes BOTH columns of data

Select Case Target.Address
    Case "$H$18"
        Cancel = True
        Randomize
        n = 1 + Int(Rnd() * Sheets("Data").Range(DataAddress).Rows.Count)
        Range("C18:E18").Formula = Array("=INDEX(Data!" & DataAddress & "," & n & ",1)", _
        "", _
        "=IF(D18="""","""",INDEX(Data!" & DataAddress & "," & n & ",2))")
    Case "$N$18"
        Range("J18:K18") = 0
    Case Else
        Exit Sub
End Select
End Sub

If you choose to use a button, here is the code for an ActiveX Control button:

Private Sub ClearButton_Click()
    Range("J18:K18") = 0
End Sub

Make sure the sub name matches the button name.

If you choose to use a Form Control button, insert the following code in a standard module and assign the macro "ClearClick" to the button:

Private Sub ClearClick()
    ActiveSheet.Range("J18:K18") = 0
End Sub

 

[RodneyW]

Alright, adjusted your "before double click" code to work for Clear Results as well as Next if you want it that way. If not, keep your current code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim n As Long

Const DataAddress As String = "D7:E128" '<- Note this includes BOTH columns of data

Select Case Target.Address
    Case "$H$18"
        Cancel = True
        Randomize
        n = 1 + Int(Rnd() * Sheets("Data").Range(DataAddress).Rows.Count)
        Range("C18:E18").Formula = Array("=INDEX(Data!" & DataAddress & "," & n & ",1)", _
        "", _
        "=IF(D18="""","""",INDEX(Data!" & DataAddress & "," & n & ",2))")
    Case "$N$18"
        Range("J18:K18") = 0
    Case Else
        Exit Sub
End Select
End Sub

If you choose to use a button, here is the code for an ActiveX Control button:

Private Sub ClearButton_Click()
    Range("J18:K18") = 0
End Sub

Make sure the sub name matches the button name.

If you choose to use a Form Control button, insert the following code in a standard module and assign the macro "ClearClick" to the button:

Private Sub ClearClick()
    ActiveSheet.Range("J18:K18") = 0
End Sub

Thank you for your continued help, I really appreciate the hand holding here. I very rarely use VBA and I think I'm missing a step.

1. I went to the Developer tab, Insert, and selected Command Button Active X and drew it where I want the button to be. See screen shot of button below.
2. This code comes with the button: =EMBED("Forms.CommandButton.1","")
3. I placed this code at the bottom of the VBA editor:

Private Sub ClearButton_Click()
Range("J18:K18") = 0
End Sub

4. From there I closed the VBA editor thinking it would work. Nothing happens with a single click. A double click opens the VBA editor.

I've tried to find the answer via Google and I'm coming up with nothing.

What am I missing?

Thanks again