# 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.

In this intense tutorial, I show you how to create this quiz or any similar project from ground up in Excel.

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:

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
Select The Best Answer From The Drop List in Column D

Format in Dark Blue ► Size & Properties

Text Box 2 ► Name it

Format in light blue ► Size & Properties

Text Box 3 ► Name it

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)

Dim QST As Range

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.