How to Make a Multiple Choice Exam Sheet in Excel

As a trainer, you would have conducted several seminars to your students. Now it’s time that you share an Excel workbook with your students which will help you to identify how much they have understood. Prepare multiple choice digital exam sheet by following this article.

Download Now

If you want to start to use the software as soon as possible, then you can:

Download the Software Now

Otherwise, if you want to DIY, you can read the contents below.

Let’s Prepare the GUI

Content

Let’s prepare the database

Add questions, options and the correct answer on the sheet “Database”

Let’s make it functional

Copy this script to a new module in your macro enabled workbook.

Sub Prepare_Test()
    Dim lr As Long
    Dim r As Long
    Dim rinq As Long
    rinq = 0
    lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    For r = 3 To lr
        rinq = rinq + 6
        Sheets("Test").Range("C" & rinq).Value = Sheets("Database").Range("A" & r).Value
        Sheets("Test").Range("C" & rinq + 1).Value = Sheets("Database").Range("B" & r).Value
        Sheets("Test").Range("C" & rinq + 2).Value = Sheets("Database").Range("C" & r).Value
        Sheets("Test").Range("C" & rinq + 3).Value = Sheets("Database").Range("D" & r).Value
        Sheets("Test").Range("C" & rinq + 4).Value = Sheets("Database").Range("E" & r).Value
    Next r
End Sub

Sub Show_Result()
    Dim lr As Long
    Dim r As Long
    Dim rinq As Long
    rinq = 0
    Sheets("Database").Visible = -1
    Sheets("Summary").Visible = -1
    lr = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    Dim v_ccount As Long
    v_ccount = 0
    For r = 3 To lr
        Dim v_answer As String
        v_answer = "Option " & Sheets("Database").Range("F" & r).Value
        rinq = rinq + 6
        If Sheets("Test").Range("C" & rinq + 1).Interior.Color = vbYellow And Sheets("Test").Range("B" & rinq + 1).Value = v_answer Then
            v_ccount = v_ccount + 1
        End If
        If Sheets("Test").Range("C" & rinq + 2).Interior.Color = vbYellow And Sheets("Test").Range("B" & rinq + 2).Value = v_answer Then
            v_ccount = v_ccount + 1
        End If
        If Sheets("Test").Range("C" & rinq + 3).Interior.Color = vbYellow And Sheets("Test").Range("B" & rinq + 3).Value = v_answer Then
            v_ccount = v_ccount + 1
        End If
        If Sheets("Test").Range("C" & rinq + 4).Interior.Color = vbYellow And Sheets("Test").Range("B" & rinq + 4).Value = v_answer Then
            v_ccount = v_ccount + 1
        End If
    Next r
    Sheets("Summary").Range("C7").Value = Sheets("Test").Range("F3").Value
    Sheets("Summary").Range("C11").Value = lr - 2
    Sheets("Summary").Range("F11").Value = v_ccount
    Sheets("Summary").Range("I11").Value = (lr - 2) - v_ccount
End Sub

Copy this script into the code window of sheet “Test”

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ar As Long
    ar = Target.Row
    Range("C" & ar & ":F" & ar).Interior.Color = vbYellow
End Sub

Copy this script into the code window of “ThisWorkook”

Private Sub Workbook_Open()
    Call Module1.Prepare_Test
    Sheets("Database").Visible = 2
    Sheets("Summary").Visible = 2
End Sub

How does it work?

When a user opens the workbook, the macro will run to hide sheets “Database” and “Summary”. If the sheet “Database” is not hidden from the user, all answers are exposed. It is also not necessary for a student to see the “Summary” sheet. Before sending the workbook to users, the macro “Prepare_Test” should be run which will populate the sheet “Test”. Users can select any option and the macro will highlight the selected answer in yellow. The macro “Show_Result” will compare selected options on sheet “Test” with answers on sheet “Database” and identifies the count of correct answers.

Have Sheets not hidden?

VBA script cannot alert properties of a corrupted Excel worksheet. In such a case, recover Excel worksheet and rerun the macro.

Author Introduction:

Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged Word and outlook recovery software products. For more information visit www.datanumen.com