Shuffle four answers of one question with a condition and loop it

[freeofcost]

In the attached excel file (version 2007), the first sheet is "quiz" and second sheet is "sheet1". It has a question/answer sheet (sheet1) that is designed in such a way that the first letter of the question is prefixed with the answer. For example here is a sample data in the range A1:A20 which is also attached. Answer to first question is "b" hence the question is prefixed with "b".

bWho is a cricketer?
Tiger Woods
Sachin Tendulkar
Will Smith
Bill Gates
aWho is a Golfer
Tiger Woods
Sachin Tendulkar
Will Smith
Bill Gates
dWho is an Entrepreneur
Tiger Woods
Sachin Tendulkar
Will Smith
Bill Gates
cWho is an actor
Tiger Woods
Sachin Tendulkar
Will Smith
Bill Gates

I need to shuffle/jumble the four options for every question (in sheet1) whenever the workbook is opened without changing the logic of prefixing answer to the question's first letter and keeping all other things intact. Please help me how can I achieve this. What kind of code can be written and which section of vba (sheet/module)? Here is an example of question # 1 how it should be after randomization (on file open).


cWho is a cricketer?
Will Smith
Bill Gates
Sachin Tendulkar
Tiger Woods


Here, the options are shuffled/jumbled and also the correct answer is updated in the question (prefixed by 'c' which was earlier 'b'). I was looking at the chip article at http://www.cpearson.com/excel/ShuffleArray.aspx but I dont know how to implement this in my question. I also tried finding source on how we can make use of these functions by Chip that suits my question to no avail. I am still trying... even if i randomize the four options, how can i get the correct answer sequence (a, b, c, d) to prefix in the question is another tough task. Appreciate some help here. Thank you!

[Bernie Deitrick]

You can try it like this:


Questions with Randomized answers.xlsm

[freeofcost]

[QUOTE=Bernie Deitrick;3889286]You can try it like this:
Thank you Mr.Deitrick. I appreciate your help and advising another approach to do it which is good enough. However, as mentioned in my first post, I need a solution without changing the logic of prefixing answer to the question's first letter and keeping all things intact. I have tried few other methods but I want to go with the one I described. I would be thankful if you can help me with that. All processing should happen only on one sheet (sheet1) without any use of formulas. The data I provided is just a sample data. The actual questions and answers are more...

[phbryan]

How about a random number generator and the choose function?

Maybe this is a step in the right direction?

[freeofcost]

How about a random number generator and the choose function?

Maybe this is a step in the right direction?

Thank you Bryan.. I do not want to go with a worksheet function concept because on every activity the options would keep shuffling. But that should be fine and can be worked out. However, my problem is that, I already have a quiz ready and it shuffles the questions without any issues but does not shuffle the answers. The file is based on the format I described in my first post and therefore I do not want to go with a different concept/approach/logic. I was looking for help in getting the sheet1 data options to shuffle on workbook open event without formulas without changing anything. If sheet1 gets changed as per my desire then the file will be ready.

Unfortunately I cannot do any logic change to the file because i will have to change a lot of things if I do so.
The logic what I thought was when the file opens is that :
1. It should take the first block of five rows (1question + 4 options),
2. check the first letter of the question (1st row) and remember which answer it corresponds to and put it in a temporary variable. (In the example first letter is 'b' that means it corresponds to row 3 (Sachin Tendulkar)
3. shuffle the four options (rows 2 to 5) and find where Sachin Tendulkar is from the first temporary variable and put it in another temporary variable.
4. prefix and replace the first letter with the new position of Sachin Tendulkar (2nd temporary variable).
5. loop it through all available questions in sheet1
I think what I asked for can be done with vba but i am unable to do so inspite of several tries. Please help me if you can.

[chris_davis]

You mentioned that you "already have a quiz ready and it shuffles the questions without any issues but does not shuffle the answers". Can you post a spreadsheet with the quiz you have ready?

[freeofcost]

You mentioned that you "already have a quiz ready and it shuffles the questions without any issues but does not shuffle the answers". Can you post a spreadsheet with the quiz you have ready?

Unfortunately I cannot because of license breach. The file is purchased with a license and I am modifying it. I have done a lot of modifications but the only thing left out is shuffling the answers without changing anything hence I posted here when I failed to do it. The only thing I can say is that if sheet1 data options are shuffled, my problem will be resolved.... Eagerly awaiting an answer. Thanks

[Bernie Deitrick]

If your column A is exactly like you showed, with the first set of questions and answers in A1:A5, then try:

Please Login or Register  to view this content.

[freeofcost]

If your column A is exactly like you showed, with the first set of questions and answers in A1:A5, then try:

Yes, it is exactly the way I had shown in my first post. Thank you so so so very much. Appreciate your assistance. God bless !

[stanleydgromjr]

Cross Posted here:
http://www.mrexcel.com/forum/excel-q...ions-quiz.html

A message to forum cross posters.

Please read this:
http://www.excelguru.ca/node/7

[freeofcost]

I completely agree... sorry about that. Infact I cross posted first time and was not aware of the rules. Henceforth I will keep it in mind. With all due respect, I never wanted to do anything wrong. My intentions were good and therefore I posted the answer so that coders should not put efforts on a solved question. I deeply regret the inconvenience.

[freeofcost]

@Bernie Deitrick Today I loaded 1000+ questions in the sheet. everything works fine with two issues:
1. screen flickers for a very long time when it is randomizing set of every question - possible solution is to set the screen updating to false
2. if the code is inserted in workbook open and before closing if the active sheet is different (not sheet1) then on opening, it gives error because the code works on activesheet - possible solution is to set the focus to shee1 on book open. Things becomes difficult if I hide "sheet1"
My question is:
1. can it be done in some kind of an array kept in memory and update the list when the calculation is over to get rid of screen flickering issue without using screen updating as a solution
2. instead of setting the focus to sheet1, can the above calculation be done no matter which sheet is the active sheet?
3. (Only if possible) a small alert saying "Loading questions. Please wait...." while the randomization is happening?

Thank You.

[Bernie Deitrick]

This should speed it up and work on just "Sheet1" no matter which sheet is active, and it gives a small warning on the statusbar:

Please Login or Register  to view this content.

[freeofcost]

Well I already did it more or less the same way. Things which i didn't know how to implement was With Worksheets("Sheet1") and the .StatusBar
I was hoping to know if there is any way to do it without the setting the screen updating to false. May be putting everything in an array, process the data (all question sets) and dump everything in the range at the end. The reason why I asked this is because, I had a bad experience an year ago where one vba program got halted in between somehow and the display alerts/events were permanently switched off. After a very long time we understood that the incident happened because of the halted program.

[Bernie Deitrick]

You can handle unexpected errors this way:

Please Login or Register  to view this content.

[freeofcost]

Thanks again... I dont want to bother you but resume next can handle unexpected errors. What I meant was if the program stops not because of errors but due to some other factors such as power failure, computer problems or any other things then the display alerts/events should not get set to OFF for excel as an application. nevertheless, you have helped me a lot. I thank you once again. Have a great time. cheers!

[Bernie Deitrick]

Any time you shut down Excel and re-start it, events and screen updating are restored to the default On values, so computer problems should not be an issue with that code. And it should run fairly quickly, too.