Create "Multiple Choice" Tests on Excel

[crilly]

Looking to have aMultiple Choice" Tests on Excel for 10 to 20 Question with a pass mark of 80%
and the answers on page 2

 

[kalikj]

And, what is your question? What specifically do you need the workbook to output?

 

[crilly]

You have up to 4 answers to a Question, you give one answers and at theend it tells you if you have all or some right I e, 5 out of 10 Question right,but the answers are on page 2 so are not seen when doing the test, so a formulaor function to make a Multiple Choice" Tests<o></o>

 

[kalikj]

This is not a question that can be answered simply as it requires multiple steps, plus use of form controls for the most elegant interface. It would take me a long time to write out the solution / process longhand!

 

[crilly]

Do you think going down the Developer route will help me create this test for the (answers & Question) and put a link onto second page? And all help is appreciated <o></o>

 

[L. Howard]

I have a modified Bingo calling workbook to ask random questions 1 to 75 in Category 1, 2, 3, 4 or 5 (your choice).

You click a button and a question from the selected category is posted in cell I4 and four possible answers are posted in cells J4 to J7.

I have not completed the answer compiling portion, nor any grading stuff nor logging the test takers name and such. Probably about 60-70% completed estimated.

Except for the example question and answers I use clinical data for the Q's Movie-2, Movie-3, Geography-1, Geography-2 and for the A's Answer Movie-2, Answer Movie-3, Answer Geo-2, Answer Geo-3 etc.

Howard

The small test taking business portion looks like this.

Excel 2012
	H	I	J
1	Choose Category	Test Taker Name > >	Albert Einstien
2	Movie
3		QUESTION IS:	?? Answer ??
4		Male Lead in "Gone with the wind?"	Clarke Gable
5			Elvis
6			Burt Reynolds
7			Tom Hanks
Sheet1

Cell Formulas
Range	Formula
I4	=VLOOKUP(H17,$L$2:$V$76,VLOOKUP($H$2,$X$2:Y6,2,0),0)

 

[crilly]

I will have a go thank you very mush

 

[L. Howard]

Okay, tweaking some formulas at present.

Do you have any ideas on how you want to "grade" the tests or any thoughts on how the results should be presented?

I will post a link to an example workbook when I get some formulas in place.

Here is the main code that goes in a standard module, probably means little to you until you have a worksheet to look at also.

Howard

Option Explicit

Sub New_Test() 'You get the idea here, I'm sure.
Dim Data As Range
Set Data = Range("B2:F16")
 Data.Interior.ColorIndex = xlNone
 Range("B18:F40,H4").ClearContents
End Sub


Sub Next_Test()
Dim i As Long
Dim j As Long
Dim k As Variant
Dim l AsLong

'this is where the focus of the code will return _
'if a number has already been called.  See the "GOTO line1" _
'in the If statement below
line1:
 

i = Int((5 * Rnd) + 1) 'produces a random number between 1 & 5
j = Int((15 * Rnd) + 1) 'produces a random number between 1 & 15
 
Application.ScreenUpdating = False
Application.EnableEvents = False

'/ limit the number of questions asked in a test session &
'/ that number is set on sheet1 cell AT3
If Range("A4").Value = [AT3] Then                                                                 '
    MsgBox "Last Question", , "No More Q's!"
    [J4:J7].ClearContents
    [H4].ClearContents
    Exit Sub
End If
        

'"i" & "j" are now a number between 1 & 5 & _
'1 & 15.  So this line will offset from cell A1 _
'the number of rows = to "j" and the numbers of _
'columns = to "i" and select that cell.
Range("A1").Offset(j, i).Select 

' sets "l" to the value of the cell that was just _
'selected in the Range("B2:F17")
l = ActiveCell.Value                   
Range("H4").Value = l


If ActiveCell.Interior.ColorIndex = 3 Then
 GoTo line1
End If

MsgBox "Question is: " & vbCr & Range("I4")
       
With Range("J4").Resize(4, 1)
 .Formula = "=VLOOKUP($H$4,$L$2:$AO$76,($AR$2)+ROW()-3,0)": .Value = .Value
End With

'If the selected cell is not red it turns it red here.
ActiveCell.Interior.ColorIndex = 3 
If ActiveCell.Column = 2 Then
        Range("B50").End(xlUp).Offset(1, 0) = ActiveCell.Value
    ElseIf ActiveCell.Column = 3 Then
        Range("C50").End(xlUp).Offset(1, 0) = ActiveCell.Value
    ElseIf ActiveCell.Column = 4 Then
        Range("D50").End(xlUp).Offset(1, 0) = ActiveCell.Value
    ElseIf ActiveCell.Column = 5 Then
        Range("E50").End(xlUp).Offset(1, 0) = ActiveCell.Value
    ElseIf ActiveCell.Column = 6 Then
        Range("F50").End(xlUp).Offset(1, 0) = ActiveCell.Value
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[crilly]

I am putting together 20 Test Type like Mock Large Vehicle Theory Test to road and Traffic signs & Drivers hours. Looking at around 80% pass mark for the tests, I have 15 written on paper but will be go into excel soon. Page one test how many the person get right, i.e. 5 out of 10, Page two hidden answers. And all helpis appreciated thank you.<o></o>

 

[ttray33y]

you can easily create test thru Google Form without coding, you can even extract scores from the result to excel file (CSV) then slice your data.