Introduction: Create Objective Type Questions in Excel

I used this kind of question paper to create sample questions for ISTQB sample papers for the candidates to test their aptitude. The same template can be used to make many other question papers in the same way. You just have to use the same template and add your own set of questions. The questions are objective type with 4 options. I got the idea from Kaun banega carorepati (an Indian version of Who wants to be a millionaire)

Step 1: Creating the Worksheets

Open Excel. Make sure you save the file as .xls (office 2003 format) as in the office 2007 and above formats the macros is not saved properly.

Create 2 worksheets named "Querstions" and "Testing Questions"
In the Questions worksheet create or copy the questions that you want to be asked, with the correct answers ofcourse.
In the cell H1 put the formula =COUNTA(G:G) (this will keep track of the questions marked to be asked)
In the cell I1 put the formula =COUNTA(A:A) (this will keep track of total number of questions)

In the Testing Questions worksheet create a button that will launch the question form.

Step 2: Creating User Form

Open VBA and create a user form.
Put 7 controls as shown in the image.
Create proper names for the controls (final file is attached)

Step 3: Write the Code

Create a macro for the Button on the worksheet
*************************
Sub Button1_Click()
Randomize
totalq = CInt(Range("Questions!I1").Text)
For i = 1 To totalq
    Range("Questions!G" & i).FormulaR1C1 = ""
Next
While (Range("Questions!H1").Text <> 10)
    i = Round(1 + ((totalq - 1) * Rnd()), 0)
    Range("Questions!G" & i).FormulaR1C1 = "A"
Wend
QForm.Show
End Sub
*************************

Also create the VBA code for the form to work
*************************
Dim counter, ans, qcounter

Private Sub Answer1_Click()
Button.Enabled = True
End Sub
Private Sub Answer2_Click()
Button.Enabled = True
End Sub
Private Sub Answer3_Click()
Button.Enabled = True
End Sub
Private Sub Answer4_Click()
Button.Enabled = True
End Sub

Private Sub Button_Click()
If (Answer1.Value = True) Then
    ans = 1
ElseIf (Answer2.Value = True) Then
    ans = 2
ElseIf (Answer3.Value = True) Then
    ans = 3
ElseIf (Answer4.Value = True) Then
    ans = 4
End If
ansacc = CInt(Range("Questions!F" & counter).Text)
If (ansacc = ans) Then
    status.Width = status.Width + 30
End If
Answer1.Value = False
Answer2.Value = False
Answer3.Value = False
Answer4.Value = False
counter = counter + 1
qcounter = qcounter + 1
If qcounter <= 10 Then
While (Range("Questions!G" & counter).Text <> "A")
    counter = counter + 1
Wend
Question.Caption = Range("Questions!A" & counter).Text
Answer1.Caption = Range("Questions!B" & counter).Text
Answer2.Caption = Range("Questions!C" & counter).Text
Answer3.Caption = Range("Questions!D" & counter).Text
Answer4.Caption = Range("Questions!E" & counter).Text
End If
Button.Enabled = False
If qcounter = 11 Then
MsgBox ("Your score is " & 10 * status.Width / 30 & "%")
QForm.Hide
End If
End Sub

Private Sub UserForm_Activate()
counter = 1
Answer1.Value = False
Answer2.Value = False
Answer3.Value = False
Answer4.Value = False
status.Width = 0
Button.Enabled = False
ans = 0
While (Range("Questions!G" & counter).Text <> "A")
    counter = counter + 1
Wend
Question.Caption = Range("Questions!A" & counter).Text
Answer1.Caption = Range("Questions!B" & counter).Text
Answer2.Caption = Range("Questions!C" & counter).Text
Answer3.Caption = Range("Questions!D" & counter).Text
Answer4.Caption = Range("Questions!E" & counter).Text
qcounter = 1
End Sub
*************************

Step 4: Execute the Code

Click the start test button. The user form should open and the options with the questions should be populated. If it does not open, that means your macro settings are not set. Kindly google for the same.

Step 5: How Does It Work

1. First 10 questions are marked at random.
2. The user form is opened and the first question is put to the user.
3. When the user selects an answer the Next button is enabled.
4. When the next button is clicked the code compares the right answer with the user's answer and if it is OK the score bar is increased.
5. This happens till all the 10 marked questions have been answered.

Step 6: Secure the Questions

Now the questions have to be hidden from the user, so that he/she should not be able to see the answers.

For doing this, In the VBA window mark its visibility as VeryHidden.
You can also protect the sheet by password protecting it etc. But that's up to you.

The attached workbook does not have these things incorporated.

Comments

author
RohanB47 made it!(author)2017-05-17

hey thanks a lot.

i needed hekp in two things:

1) how should i remove the randomness of questions.

2) what should i do if i want the options selected to be stored in the excel sheet

author
vparab710 made it!(author)2016-12-06

Hi,

Any idea how do we store the scores if there are 50 concurrent users giving the same test?

author
artworker made it!(author)2016-12-06

Got some idea here http://www.rondebruin.nl/win/s1/outlook/mail.htm

But still have to try this out. Please post if you get anything fruitful.

author
jgeekw made it!(author)2011-12-20

How would you create a "complete" button, that when the test taker is done, that outputs their answers along with their score to another file (or even to an email address), or to a printer?

author
artworker made it!(author)2011-12-21

You see, I am a lazy guy. The code for what you ask is available on google. But It will take some time to get incorporated into the code. I will surely add those in v2.0.

author
vparab710 made it!(author)2016-12-06

Hi,

Any idea how do we store the scores if there are 50 concurrent users giving the same test?

author
unni1986 made it!(author)2016-11-29

Hi, Iam new vba any one help me what are 7 controls which was added to the userform plz

author
artworker made it!(author)2016-12-01

The default userform controls in excel VBA are

Label: used for showing text to user

TextBox: used to take user input

ComboBox: Dropdown list

ListBox: Scrolling list (can be multi selection)

CheckBox: Used to take tick marks (multi selection)

OptionButton: Used to take tick marks (single selection)

ToggleButton: On off switch

Frame: Used to group controls

CommandButton: ok/cancel/submit or any other button

TabStrip: Used for multi tab effect

MultiPage: Used for multi form effect

ScrollBar: Used to scroll/slider

SpinButton: Used as counter

Image: Insert pics

RefEdit: External controls like file read wizard

I have used: Labels, RadioButtons and CommandButtons in the above user form.

Hope this helps.

author
maxtimberlake made it!(author)2016-11-26


http://www.excelfilepasswordrecovery.com

author
satish_karnan2012 made it!(author)2016-05-04

Hi team,

Can we copy the test result of one person in different sheet to Validate. and also can we add timer (for Ex. for 30 mins) it should end the test.

Please help!

Thanks & Regards

Satish

author
DeepakG43 made it!(author)2016-03-25

hi... i have completed and found the answer..tks

another question i would like to ask is on the present page is it possible to attach pictures as objective questions.....

author
artworker made it!(author)2016-03-31

Yes! This can be done. But some addins are required for images to be ported from the excel sheet to the VBA forms. Have to do some R&D. This is out of my scope for now. Will make another ible if get to know about this feature.

author
DeepakG43 made it!(author)2016-04-03

tks..

also i would like to know on the prsent page as im setting up something for my office staff as soon as the test starts will it be possible for the staff to enter their names and after the exam/test their percentage is auto printed on as a PDF file which can be generated as an email.

author
DeepakG43 made it!(author)2016-03-17

hi. i would like to know how to change the mark percentage for the sheet .. i am giving 50 questions .. i need to get after the exam total percentage of 100%. how to indicate it..

author
LeonardoA39 made it!(author)2016-02-16

Sir how can I change the questions into 10? I made a questions of 127 items and marked the questions to 50 random but when I tried to start the questions it appears only 10 random questions. Please help me Sir.

author
artworker made it!(author)2016-02-17

Change the line

While (Range("Questions!H1").Text <> 10)

to

While (Range("Questions!H1").Text <> 50)

If qcounter <= 10 Then

to

If qcounter <= 50 Then

AND

If qcounter = 11 Then

to

If qcounter = 51 Then

The scoring thing will still have a glitche.

This is a temporary solution. I will have to make a newer improved customizable version of the question paper.

author
ashishpatel258 made it!(author)2016-01-31

Wow superb work sir. I was searching similar from last week. I would like to have one more feature. I am planning to make around 1000 questions in excel, and then select some questions from them, say 25, and print a small test. In this way we will be able to make different questions in each test without copy pasting anything.

For print we can use sheet2 and make some code that will fetch questions with 'A' in column G. I dont know how to write that code or formula. If you can write it will be very useful.

author
mlikewise made it!(author)2015-11-14

How do you change it so it doesn't do just 10 questions randomly? I would like to do 50 questions randomly.

author
artworker made it!(author)2015-11-22

Please see inbox.

author
satigopa made it!(author)2016-01-19

Can you please help me too. How do you change it so it doesn't do just 10 questions randomly? I would like to do 50 questions randomly.

author
SanuK1 made it!(author)2015-12-14

How do you change it so I can have more than 10 questions say 30 or 50?

author
artworker made it!(author)2015-12-14

Change the code in Sub Button1_Click()

While (Range("Questions!H1").Text <> 10)

to

While (Range("Questions!H1").Text <> 30)

author
SanuK1 made it!(author)2015-12-14

How do you change it so I can have more than 10 questions say 30 or 50?

author
amitnarula made it!(author)2015-08-29

A very interesting and useful instructable. Thanks

author
ebincharles made it!(author)2015-07-25

Hello, it is really nice.

Can anyone try to highlight the correct answer with green background. If you click wrong answer, the right answer should be highlight with green and pass to next question. Possible?

author
artworker made it!(author)2015-07-27

Easier option! You can show the correct answer in a message box. Just one or two lines has to be added in the code.

author
OlaitanK made it!(author)2015-05-27

author
OlaitanK made it!(author)2015-05-28

Thanks got it.

author
OlaitanK made it!(author)2015-05-27

Thanks for the short lecture. Please can you explain what the changes one has to make to the program to accommodate 5 choices to choose from?

author
artworker made it!(author)2015-05-27

Insert new answers in column F

Add a new control in the form as Answer5

Add the following code where required

***************code begin

.
.
.
Private Sub Answer5_Click()
Button.Enabled = True
End Sub
.
.
.
ElseIf (Answer5.Value = True) Then
ans = 5
.
.
.
Answer5.Value = False
.
.
.
Answer5.Caption = Range("Questions!F" & counter).Text

**************code end

Edit the remaining code as per the changes in the excel such as

F,G,H and I will change to G,H,I and J (because a column has been inserted)

That is about it! (am I missing something?)

Please comment if you are stuck.

author
wilgubeast made it!(author)2011-12-20

This is completely and totally awesome. You, sir, are an Excel ninja. A light, a beacon for those teachers who are tired of writing a test, grading that test, doing data entry for an hour, and finally seeing how their students performed.

Revolutionary. Now, we just need a version for those teachers who have previously only ever used Powerpoint and Word.

About This Instructable

40,298views

26favorites

License:

Bio: I am a hobist with lots of hobies from carpentry to mechanics. There is hardly any thing that i am not interested in.
More by artworker:Worlds smallest fidget inspired by big hero 6 microbotCustomizable wooden toyStitch Your Own Jewelry
Add instructable to: