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 am not able to implement this in my question. I also tried finding source on how we can make use of these functions 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! -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
random.xlsx
Description: MS-Excel 2007 spreadsheet