Answer Sheet in Excel

itsmereyyy

New Member
Joined
Dec 2, 2017
Messages
6
Hi. I'm new in Excel VBA, and I'm trying to compare the value in a certain cell in sheet 1 and a certain cell in sheet 2 and when I get the same value, it will print "Correct" or "Wrong" on another cell. I've been visiting various websites and didn't get what I was looking for (I'm a newbie btw.) Ex. In cells C9 to C18 in sheet 1, I have questions there, in D9 to D18 same sheet, the user will type in their answer, and in Cells C4 to C13 in sheet 2, the correct answers are there. So when I press this "Check" button, it should compare the answer given by the user and the correct answer, and if the answers match, there will be a "Correct" value or if not "Wrong" in cells D9 to D18 in Sheet 1. It's like a questionnaire with checker. Thank youuuuuuuuuuu! It's greatly appreciated. This is our preliminary examination. I hope you can help :) Thanks!

This is what it looks like. It's what I have done.

https://prnt.sc/hi1uxd
https://prnt.sc/hi1uxn
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
In the Question sheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D9:D18")) Is Nothing Then
        If Target.Value = Sheets("Answer Key").Cells(Target.Row - 5, 3).Value Then
            Target.Offset(0, 1).Value = "Correct"
        Else
            Target.Offset(0, 1).Value = "Wrong"
        End If
    End If
End Sub
 
Last edited:
Upvote 0
what sort of answers do you have
 
Upvote 0
Couple of ideas

First is a straight up comparison between the questionnaire and answer key using your commandbutton (Which I called cmbCheck)

Code:
Private Sub cmbCheck_Click()
Dim i As Integer
For i = 1 To 10
    Me.Range("E" & i + 8) = IIf(Me.Range("D" & i + 8) = Sheets("Answer Key").Range("C" & i + 3), "Correct", "Wrong")
Next i
End Sub

Second would have a formula in the remark cell which doesn't show if an arbitrary cell is set to false (I use cell A1 which can be formatted to not be visible)

Code:
=IF($A$1, IF(D10='Answer Key'!C5, "Correct", "Wrong"), "")

Then have your check button switch it to true, the clear button to reset is to false

Code:
Private Sub cmbCheck2_Click()
Me.Range("A1") = 1
End Sub


Private Sub cmbClear_Click()
Me.Range("A1") = 0
End Sub

HTH
 
Upvote 0
what sort of answers do you have

in the Answer column in Questionnaire Sheet and in the Answer Key sheet. So what ever I put in the answer key, it will be compared to the user-input answer and get a remark of wrong or correct. That's what I wanna know.
Nothing. I just want to know how to compare the values
 
Upvote 0
Nothing. I just want to know how to compare the values in the Answer column in Questionnaire Sheet and in the Answer Key sheet. So what ever I put in the answer key, it will be compared to the user-input answer and get a remark of wrong or correct. That's what I wanna know.
 
Upvote 0
some queries rely on case sensitivity, and additional spaces will return right and wrong i.e Monday and monday could be different, hence why I asked, if the answer in several words then you enter a minefield of detecting correct as nearly everything will be wrong
 
Upvote 0
at its most basic B1 on two sheets

> =IF(B1="","",IF(B1=Sheet2!B1,"Correct","wrong"))
 
Upvote 0
In the Question sheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D9:D18")) Is Nothing Then
        If Target.Value = Sheets("Answer Key").Cells(Target.Row - 5, 3).Value Then
            Target.Offset(0, 1).Value = "Correct"
        Else
            Target.Offset(0, 1).Value = "Wrong"
        End If
    End If
End Sub

This worked, however, it appears straight after the user input the answer. Is there any way to mod the code so that the remarks will pop up once I click the "Check" button? I can't seem to find a solution with this in the net.
 
Upvote 0
some queries rely on case sensitivity, and additional spaces will return right and wrong i.e Monday and monday could be different, hence why I asked, if the answer in several words then you enter a minefield of detecting correct as nearly everything will be wrong

There is a way where I could make the typed or input value in automatic upper/lowercase letter, right? I might use that so. I just need to explore more in excel. I'm a beginner in vba, sorry. And our task is just make a basic excel/vba program that checks the answer if it is correct or wrong. Thank you for the help! :)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top