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

questions	anwsers	right or wrong
how many grams in a kilogram
Which of these is not a colour

<tbody>
</tbody>

Questions and answers sheet table where answer abolute isn't used for the data validation but for the right wrong check

question number	question	answer absolute	true answer	falseanswer1	falseanswer2	falseanswer3
1	how many grams in a kilogram	1000	1000	900	500	530
2	Which of these is not a colour	hdd	hdd	red	yellow	pink

<tbody>
</tbody>

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