• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Prepare Question bank

vrunda

Member
Want to prepare a dynamic question Bank In excel. Sheet 1 will have Questions with 4 options as answers. Sheet 2 will have correct answers for every question.

Requirements is: On sheet 1 Every time excel is opened The questions will change their sequence so on every computer Q1 will not be same, Q2 will not be same i.e. Every question is reshuffled along with its 4choices of answers. Sheet 1 will also contain Submit button that will show result i.e marks of that PC/Student. Submit button will not be for every question.

I hope I m clear
 
https://dl.dropboxusercontent.com/u/53850800/Question%20Bnak.xlsx


Sample file. But it does not contain buttons yet. Reshuffling of questions is shown.
 
Can't access files from my location, but here's the thought process I would use.

Setup some cells somewhere with RAND() formulas to just generate the random order. You can then use the RANK function if you want to more easily see what order. You should have 1 question corresponding to each of these cells.


You'll then write a Workbook_Open event macro that copies the cells with formulas and pastes them as values (to lock in the choices). You can then use INDEX or VLOOKUP type functions to list your questions on Sheet 1 based on the order that was randomly generated.


I know this isn't my usual level of detail, but time was crunched. Hope this helps you, or one of the other contributors.
 
I thought to use vlookup/ index function to sr.nos column rather than question column. So I made two columns for sr.no as shown in workbook.

Secondly how & why to use Rank function ?

3) copy paste answers to lock them .... Did not get this point? How it will be useful?


What I need to do to send the workbook.
 
Dear vrunda,

find the link and customize as you want. I have given just idea.


http://www.2shared.com/file/nxX4VOEl/Random_Question_and_Score-MNM.html


Regards,


Muneer
 
Hi Vrunda ,


Your problem can be done wonderfully well using forms and VBA ; however , there is enough material on the Internet , which you may find useful ; one such is here :


http://homepage.ntlworld.com/neil.williams50/free-resources2.htm


Regarding your question of presenting the questions in random order , one simple way is to use a helper column next to your question bank ; of course , it would be better if you could structure your question bank , so that the serial number , the question description , the answer choices are all in the same row , in 6 columns ( assuming that each question has 4 answer choices ).


In the helper column , put in the formula =RANDBETWEEN(1,9999999) , and copy it down for the 500 ( or remaining 499 ) rows ; each time you open the workbook , all you have to do is sort your data range using the helper column as the sort order.


Narayan


P.S. Two other related links are :


http://www.wondershare.com/quiz-maker/make-easy-quiz.html


http://www.howe-two.com/teacher/QuizRandom/index.html
 
Hi, vrunda!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Prepare%20Question%20bank%20%28for%20vrunda%20at%20chandoo.org%29.xlsm


I've adapted a workbook created for exams or tests to be as close to your requirements as possible. Check it to see if it's suitable for your needs.


The worksheets are protected without password and the VBA project password is "NoTocaBoton" unquoted.


Just advise if any issue.


Regards!
 
Sir nazmul_muneer,

Sheet Question series is for which purpose i did not understand. In workbook open event the code reshuffles the questions. So how the question series sheet helps . & moreover for 500 questions how to use that sheet.

From 500 questions only 200 are to be displayed so how to modify?
 
Narayan Sir How to use the Wondershare site.? Then using user forms i m trying to do in excel. Thanks for guiding for helper column.
 
Hi Vrunda ,


You can download the Excel template ( quiz-template.xls ) and prepare your questions bank.


You also need to download the software ( quizcreator_full31.exe ) , install it , and then run it.


Narayan
 
Hi, vrunda!

Fixed. Sorry for the inconvinience, please download again the updated file:

https://dl.dropboxusercontent.com/u/60558749/Prepare%20Question%20bank%20%28for%20vrunda%20at%20chandoo.org%29.xlsm

Regards!
 
Sorry , I was out of station.

I checked today & now have to modify accordingly.

SirJB7,: When workbook is opened there is some object error. But still the workbook works fine. Is it due to some controls I have to enable? . What is the reason?
 
Sir Nazmul_muneer,

Sheet Question series is for which purpose i did not understand. In workbook open event the code reshuffles the questions. So how the question series sheet helps . & moreover for 500 questions how to use that sheet.

From 500 questions only 200 are to be displayed so how to modify?
 
Narayan Sir,


I am using Randbetween function in 499 rows but it is repeating questions.

Secondly if i have 500 questions & want to display any 200 of them to any student then how it can be done?
 
Hi, vrunda!

Would you please paste here the error you get displayed? You'll have to do it twice as first time you should unprotect the VBA project so as to could enter in debug mode and post the line where the error raises.

Regards!
 
Hi Vrunda ,


I have earlier explained how to get a random order of the questions each time you open the workbook ; let me try one more time.


Let us assume you have just one column of data , which is just a serial number from 1 through 500 ; let us assume this serial number is in column A.


In column B , use the formula =RANDBETWEEN(1,99999999999) ; the large band ( between 1 and 99999999999 ) is just to ensure that the chances of a number repeating are as small as possible.


When you copy this formula over 500 rows , you will end up with 500 random numbers , with possible repetitions.


Now sort your data ( columns A and B ) in the order of the numbers in column B ; this will automatically ensure that your serial numbers in column A are thoroughly randomized , since even if there are repetitions in column B , they will not occur with consecutuve serial numbers in column A ; thus if there are two identical random numbers in B17 and B55 , when you sort , the serial numbers in column A which are together will be 17 and 55.


Since the RANDBETWEEN function is volatile , each time you open the workbook , the random numbers will be different , and consequently , the order of the serial numbers will also be different.


As far as taking 200 questions out of these 500 is concerned , since the questions are anyway in random order , always take the first 200 questions , that is all.


Narayan
 
Hi, vrunda!

Are you still getting the reported error? If so, please follow my previous post indications so as to further assist you.

Regards!
 
Sir JB7,

Now it is not giving error while opening. So I think it is done.

I am preparing Question Bank & taking some points from everywhere. Your Question Bank is working correctly except I am unable to understand some coding as i m learning VBA. & if i copy whole code & use it then my skills wont improve. I woud come again if i need some help. ThankYou!!!

Narayan Sir ,

What u explained i did practically & now by coding I shall sort col A & col B . Because it should automatically do everything & calculate marks too.
 
I am stuck at timer for quiz. I need the one hour timer should run backwards & on sheet the mins should be seen decreasing.
 
I got the countdown timer but the Question sheets is moving every second, which is disturbing / distracting eyes.

How can we make the sheet to be static?
 
Hi, vrunda!

Have you added a timer feature to my uploaded workbook or are you using one of NARAYANK991's suggestion? If it's the first case please upload the updated file; if it's the last one, proceed similarly unless any software requires to be installed.

Regards!
 
I have prepared one from your all suggestions & guidance. It is incomplete. Pls have a look at it. & amend it.

I need the sheet should be static , while the timer is running.

& the user form is displayed even though I unload it in code. Why so?


https://dl.dropboxusercontent.com/u/53850800/Vrunda%20Q%20B.xlsm
 
Back
Top