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
1