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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Vbagreenhorn1

Board Regular
Joined
Feb 3, 2017
Messages
125
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:

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
what sort of answers do you have
 

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
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
 

itsmereyyy

New Member
Joined
Dec 2, 2017
Messages
6

ADVERTISEMENT

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
 

itsmereyyy

New Member
Joined
Dec 2, 2017
Messages
6
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.
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
at its most basic B1 on two sheets

> =IF(B1="","",IF(B1=Sheet2!B1,"Correct","wrong"))
 

itsmereyyy

New Member
Joined
Dec 2, 2017
Messages
6
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.
 

itsmereyyy

New Member
Joined
Dec 2, 2017
Messages
6
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,396
Messages
5,601,414
Members
414,449
Latest member
Pashtun

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
Top