data validation from multiple choice questions

[tridento]

Hi
I'm creating multiple choice test and i have everything done apart from the answer data validation. The problem is the questions are from a question bank of around 40 which 10 are randomly chosen and i cant figure how to create a dynamic data validation.
Main sheet table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]questions[/TD]
[TD]anwsers[/TD]
[TD]right or wrong[/TD]
[/TR]
[TR]
[TD]how many grams in a kilogram[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Which of these is not a colour[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Questions and answers sheet table where answer abolute isn't used for the data validation but for the right wrong check
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]question number[/TD]
[TD]question[/TD]
[TD]answer absolute[/TD]
[TD]true answer[/TD]
[TD]falseanswer1[/TD]
[TD]falseanswer2[/TD]
[TD]falseanswer3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]how many grams in a kilogram[/TD]
[TD]1000[/TD]
[TD]1000[/TD]
[TD]900[/TD]
[TD]500[/TD]
[TD]530[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Which of these is not a colour[/TD]
[TD]hdd[/TD]
[TD]hdd[/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[TD]pink[/TD]
[/TR]
</tbody>[/TABLE]

Public Sub commandbutton1_click()
Dim i As Integer, RowNum As Integer, rList As String

'Sheets("Welcome").Visible = False
Sheets("Main").Range("A:D").ClearContents
Sheets("Sheet3").Range("B:B").ClearContents
Sheets("Sheet3").Range("A:A").ClearContents

Random_Question = Sheets("Questions_and_Answers").Range("G2")
Random_Questions = Random_Question + 1
Question_bank = Sheets("Questions_and_Answers").Range("F2")
Question_banks = Question_bank + 1

For i = 2 To Question_banks

generate:
RowNum = Application.RoundUp(Rnd() * Random_Questions, 0)
If RowNum = 1 Then
GoTo generate

ElseIf Application.CountIf(Sheets("Main").[A:A], Sheets("Questions_and_Answers").Cells(RowNum, "B")) = 0 Then

Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Questions_and_Answers").Cells(RowNum, "A").Value ' question numbers


Sheets("Main").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Questions_and_Answers").Cells(RowNum, "B").Value ' questions



Sheets("Main").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "=IF(B" & i & "=Questions_and_Answers!C" & RowNum & ", ""correct"", ""try again"")" ' result

Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "=IF(Main!C" & i & "=""correct"",1,0)" ' result value 1 is correct o is wrong


Else
GoTo generate
End If

Next i

Sheets("Sheet3").Select
Range("A1").Value = "Question Number"
Range("A1").Font.Bold = True
Range("A:A").Columns.AutoFit
Range("B1").Select
Range("B1").Value = "Answers Correct"
Range("B:B").HorizontalAlignment = xlCenter
Range("B:B").Columns.AutoFit


Sheets("Main").Select

Range("A1").Value = "Questions"
Range("A1").Font.Bold = True
Range("A:A").HorizontalAlignment = xlCenter
Range("A:A").Columns.AutoFit

Range("B1").Select
Range("B1").Value = "Answer The Questions"
Range("B1").Font.Bold = True
Range("B:B").HorizontalAlignment = xlCenter
Range("B:B").Columns.AutoFit
End Sub

 

[tridento]

might have solved this
so after

Sheets("Main").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Questions_and_Answers").Cells(RowNum, "B").Value ' questions

but before

Sheets("Main").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "=IF(B" & i & "=Questions_and_Answers!C" & RowNum & ", ""correct"", ""try again"")" ' result

i added

Set Rng = Sheets("Main").Range("B" & i)

cellToBeReferred = "Questions_and_Answers!D" & RowNum & ":G" & RowNum & ""


With Rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=" & cellToBeReferred & ""
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

so it loops with the script adding the data validation from the question and answer sheet to the main page rows which change when i value changes also when more questions are added it still works - which is a plus
i think i'll have a pint - i just started learning about excel vba a month ago and i'm chuffed i've gotten this far. thanks