I'm creating a macro enabled form to create multiple choice test. I have 2 tabs - "Sim" for the testing page and "library" for where all my data is stored. I have a random function to pull at random the row where the question is: "library -column a" is the question, "library - column b" is answer 1 and so forth. What I'm having trouble wrapping my mind around is how do I put the 4 answers choices into an array then shuffle them so that each time the question is shown on the Sim sheet the list of possible answers are in a different order?
This is the code I have so far:
This is the code I have so far:
Code:
Private Sub CommandButton1_Click() 'generates question and 4 possible answers to choose from.
NQ = Sheets("library").Range("A1").End(xlDown).Row
Sheets("Sim").Range("B5:L17").ClearContents
RowNum = Application.RoundUp(Rnd() * NQ - 1, 0)
Cells(1, NQ).Value = RowNum
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(4).Value = Sheets("library").Cells(RowNum, "A").Value 'question
'---------------------
'The next 4 values from the row in the library sheet need to be put into an array, shuffled, then inserted into the Sim sheet.
'The library sheet is never to be modified. The below just shows a non shuffled way of populating the fields. In the
'actual sim the below 4 lines are not use.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "B").Value 'answer from column b in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "C").Value 'answer from column c in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "D").Value 'answer from column d in the library sheet.
Sheets("Sim").Range("B" & Rows.Count).End(xlUp).Offset(2).Value = Sheets("library").Cells(RowNum, "E").Value 'answer from column e in the library sheet.
'-----------------------
'insert method to add the 4 possible answers that are shuffled here
'-----------------------
End Sub