The below formula is being used to create a test based off of two columns in another worksheet. The test is a language test, from Korean to English or vice verse. What the Subroutine does is checks the worksheet, and pulls a random word from the appropriate Column, and puts that word in the text box. It then chooses the answer to that word, and three other random words from the opposite column and assigns them to the option buttons. The user then checks the option button that matches the translation of the word in the text box. If the checked button is correct, the msgbox displays correct, and a new word possible answers are displayed. If it is wrong, the msgbox displays incorrect and allows the user to choose a new answer.
The problem I am having is that, when the program loads the possible answers, every once in a while I get a repeat of the possible answer, or a blank answer with no value in it. I can't seem to figure out why it is doing this.
The code I am using is below. I am sorry it is so long, but I am a novice excel user, self taught. So, I don't know how to make it any shorter.
If anyone can fix my code to not repeat a possible item in the appropriate column, and to not show a blank answer, I would greatly appreciate it.
Thank you in advance.
The problem I am having is that, when the program loads the possible answers, every once in a while I get a repeat of the possible answer, or a blank answer with no value in it. I can't seem to figure out why it is doing this.
The code I am using is below. I am sorry it is so long, but I am a novice excel user, self taught. So, I don't know how to make it any shorter.
Code:
Sub Create_Question()
If Userform2.ComboBox1.Value = "" Then
MsgBox "Please Set Preferences First", vbOKOnly
UserForm1.Hide
Exit Sub
End If
If Userform2.ComboBox2.Value = "" Then
MsgBox "Please Set Preferences First", vbOKOnly
UserForm1.Hide
Exit Sub
End If
Application.ScreenUpdating = False
Dim topic As String
Dim language As String
Dim target As Range
Dim Question As String
Dim sOut As String, d As Object, r As Integer, N As Long
Dim Answer1 As Integer
Dim Answer2 As Integer
Dim Answer3 As Integer
Dim Answer4 As Integer
'combobox 1 from userform 2 allows the user to select a topic based on sheet names
topic = Userform2.ComboBox1.Value
'combobox 2 values are for translating English to Korean, or Korean to English
language = Userform2.ComboBox2.Value
UserForm1.Caption = topic
'sets column to be used
If language = "Korean > English" Then
language = "Korean"
Else
If Userform2.ComboBox2.Value = "English > Korean" Then
language = "English"
End If
End If
'Chooses random number to assign to option buttons
Set d = CreateObject("Scripting.dictionary")
d.RemoveAll
Do
r = WorksheetFunction.RandBetween(1, 4)
If Not d.exists(r) Then
N = N + 1
If N = 1 Then
Answer1 = r
Else
If N = 2 Then
Answer2 = r
Else
If N = 3 Then
Answer3 = r
Else
If N = 4 Then
Answer4 = r
End If
End If
End If
End If
d.Add r, N
sOut = sOut & vbNewLine & r
End If
Loop Until N = 4
'tells program what to do if the user wants to translate Korean to English
If language = "Korean" Then
UserForm1.TextBox1.Font.Size = 26
'count number of nonblank cells within selected topic's worksheet
N = Worksheets(topic).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
'sets range to choose random value from
Worksheets(topic).Select
Range("A2:A" & N).Select
Set target = selection
'assign random cell value to variable "Question" and put the question in the textbox
Question = selection(Int(Rnd * N) + 1, 1)
UserForm1.TextBox1.Value = Question
'Finds the Answer to the Question
For Z = 1 To N
If Range("A" & Z) = Question Then
answer = Range("A" & Z).Offset(0, 1)
End If
Next Z
'Assigns caption to Option Button 1 based on random number generated above
If Answer1 = 1 Then
UserForm1.OptionButton1.Caption = answer
Else
Worksheets(topic).Select
Range("B2:B" & N).Select
Set target = selection
UserForm1.OptionButton1.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 2 based on random number generated above
If Answer2 = 1 Then
UserForm1.OptionButton2.Caption = answer
Else
Worksheets(topic).Select
Range("B2:B" & N).Select
Set target = selection
UserForm1.OptionButton2.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 3 based on random number generated above
If Answer3 = 1 Then
UserForm1.OptionButton3.Caption = answer
Else
Worksheets(topic).Select
Range("B2:B" & N).Select
Set target = selection
UserForm1.OptionButton3.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 4 based on random number generated above
If Answer4 = 1 Then
UserForm1.OptionButton4.Caption = answer
Else
Worksheets(topic).Select
Range("B2:B" & N).Select
Set target = selection
UserForm1.OptionButton4.Caption = selection(Int(Rnd * N) + 1, 1)
End If
Else
'Tells Program what to do if user wants to translate English to Korean
If language = "English" Then
UserForm1.TextBox1.Font.Size = 14
UserForm1.TextBox1.WordWrap = True
'count number of nonblank cells within selected topic's worksheet
N = Worksheets(topic).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
'sets range to choose random value from
Worksheets(topic).Select
Range("B2:B" & N).Select
Set target = selection
'assign random cell value to variable "Question" and put the question in the textbox
Question = selection(Int(Rnd * N) + 1, 1)
UserForm1.TextBox1.Value = Question
'Finds the Answer to the Question
For Z = 1 To N
If Range("B" & Z) = Question Then
answer = Range("B" & Z).Offset(0, -1)
End If
Next Z
'Assigns caption to Option Button 1 based on random number generated above
If Answer1 = 1 Then
UserForm1.OptionButton1.Caption = answer
Else
Worksheets(topic).Select
Range("A2:A" & N).Select
Set target = selection
UserForm1.OptionButton1.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 2 based on random number generated above
If Answer2 = 1 Then
UserForm1.OptionButton2.Caption = answer
Else
Worksheets(topic).Select
Range("A2:A" & N).Select
Set target = selection
UserForm1.OptionButton2.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 3 based on random number generated above
If Answer3 = 1 Then
UserForm1.OptionButton3.Caption = answer
Else
Worksheets(topic).Select
Range("A2:A" & N).Select
Set target = selection
UserForm1.OptionButton3.Caption = selection(Int(Rnd * N) + 1, 1)
End If
'Assigns caption to Option Button 4 based on random number generated above
If Answer4 = 1 Then
UserForm1.OptionButton4.Caption = answer
Else
Worksheets(topic).Select
Range("A2:A" & N).Select
Set target = selection
UserForm1.OptionButton4.Caption = selection(Int(Rnd * N) + 1, 1)
End If
End If
End If
Worksheets("Flash Cards").Activate
Application.ScreenUpdating = True
End SubIf anyone can fix my code to not repeat a possible item in the appropriate column, and to not show a blank answer, I would greatly appreciate it.
Thank you in advance.
