Building A Quiz, Survey or Questionnaire … Step by Step
Building Quizzes, Surveys and Questionnaires either to test knowledge on any topic or collect answers… is extremely common and useful.
In a previous tutorial, I shared an Excel Quiz used to test the Excel level for candidates applying for a Job. It consists of 40 Multiple choice questions. The Quiz can be marked with a click and reveal the score and rating of the candidate.
You can download that file about building surveys and quizzes and take the quiz at your convenience, by clicking on the link here below:
In this intense tutorial, I show you how to create this quiz or any similar project from ground up in Excel.
You can download the start File and follow along by clicking on the link here below:
We’ll be combining lots of functionalities in this project, such as:
Data Validation, Functions, Conditional Formatting, Charts, Macros, protection and much more… it’s a Power-Packed recipe full of Excel Vitamins and Minerals
So let’s dive in
Here are the Steps and the VBA Codes used
I got the questions and answers from this website:
https://thoughtcatalog.com/kelly-peacock/2020/04/multiple-choice-trivia-questions-and-answers/
Entering Quiz Questions, 4 options and Answers ►Hide Column L
Create a Drop List in D2 ►Copy down to D41
Create a Function in Column F =IF(F2=””,””,IF(D2=L2,1,0))
Create Conditional Formatting in D2 : D41 and test it:
=F2=”” ►No Fill Color
=AND(F2=0,$XFD$1=1) ►Red Fill Color
=AND(F2=1,$XFD$1=1) ►Green Fill Color
Calculations:
X1 ► Sum(F2:F41)
Y1 ► ??/40
Z1 ► =”Your Score Is “& CHAR(10) &Y1
X2 ►=”You Are A(n) “& CHAR(10) &VLOOKUP(X1,X4:Y8,2,1) & ” of Excel”
Y10 ► =COUNTIF($F$2:$F$41,1)
Y11 ► =COUNTIF($F$2:$F$41,0)
Creating a Text Boxes:
Text Box 1 ► Name it
Reads: You Have 40 Questions To Answer in 30 Minutes
Select The Best Answer From The Drop List in Column D
Format in Dark Blue ► Size & Properties
Text Box 2 ► Name it
Reads: =$Z$1
Format in light blue ► Size & Properties
Text Box 3 ► Name it
Reads: =$X$2
Format in light blue ► Size & Properties
Chart 1 ► Name it
Source: X10:Y11
No Title, No Fill, Top Legend, resize Plot Area, Properties
Align and Position Text Box 2, Text Box 3 and Chart 1
Hide Columns F to L & X to Z
Insert 3 Pictures ► Set their Size & Properties
Automation 2 – 3 codes
Code one
Sub StartTest()
Range(“D2:D41”).Interior.Color = xlNone
Range(“D2:D41”).ClearContents
Range(“XFD1”).ClearContents
Sheets(1).Shapes(“Chart1”).Visible = FALSE
Sheets(1).Shapes(“TextBox2”).Visible = FALSE
Sheets(1).Shapes(“TextBox3”).Visible = FALSE
Sheets(1).Shapes(“TextBox1”).Visible = TRUE
Range(“D2”).Select
End Sub
Code Two
Sub SeeResults()
Range(“D2:D41”).Interior.Color = xlNone
If Range(“XFD1”) = 1 Then
Exit Sub
End If
Range(“XFD1”).Value = 1
Columns(“XFD”).Hidden = True
Sheets(1).Shapes(“Chart1”).Visible = TRUE
Sheets(1).Shapes(“TextBox2”).Visible = TRUE
Sheets(1).Shapes(“TextBox3”).Visible = TRUE
Sheets(1).Shapes(“TextBox1”).Visible = FALSE
End Sub
Code Three (Optional)
Sub AnswerExam()
Dim AnswerRange As Range
Dim QST As Range
Set AnswerRange = Range(“D2:D41”)
For Each QST In AnswerRange
QST.Value = QST.Offset(0, 8).Value
Next
Range(“D2:D41”).Interior.Color = RGB(146, 208, 80)
End Sub
Protection and Testing
Unlock the Range D2:D41 and XFD1
Protect Worksheet
Protect Workbook
Protect VBA Code ► Must save and reopen.
You can watch the video Tutorial on my YouTube Channel
