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:
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