PDA

View Full Version : VBA script not working what have I missed



trebor58
11-16-2017, 05:17 AM
Hi

I copied a VBA script from a Youtube video showing how to make a "VBA Excel Lottery Numbers Generator"

This will generate random numbers, no repeats and then add them to an excel sheet.

Below is the full script, which as I said I copied from Youtube, however, I have modified so it becomes a draw number selector.

The scripts work except for the adding to the spreadsheet I get a Run-time error '424': Object required, when I hit debug it highlights the line of code as red bold below. I have tried contacting the person who made the video but he has no contact details.

I appreciate any help to get this working.


Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim addItem As Range


TextBox1.Text = Evaluate("RANDBETWEEN(300, 400)")
TextBox2.Text = Evaluate("RANDBETWEEN(300, 400)")
TextBox3.Text = Evaluate("RANDBETWEEN(300, 400)")
TextBox4.Text = Evaluate("RANDBETWEEN(300, 400)")
TextBox5.Text = Evaluate("RANDBETWEEN(300, 400)")
TextBox6.Text = Evaluate("RANDBETWEEN(300, 400)")


If TextBox1.Value = TextBox2.Value Or TextBox1.Value = TextBox3.Value Or TextBox1.Value = TextBox4.Value _
Or TextBox1.Value = TextBox5.Value Or TextBox1.Value = TextBox6.Value Then
TextBox1.Value = TextBox1.Value + 1
ElseIf TextBox2.Value = TextBox1.Value Or TextBox2.Value = TextBox3.Value Or TextBox2.Value = TextBox4.Value _
Or TextBox2.Value = TextBox5.Value Or TextBox2.Value = TextBox6.Value Then
TextBox2.Value = TextBox2.Value + 1
ElseIf TextBox3.Value = TextBox1.Value Or TextBox2.Value = TextBox3.Value Or TextBox3.Value = TextBox4.Value _
Or TextBox3.Value = TextBox5.Value Or TextBox3.Value = TextBox6.Value Then
TextBox3.Value = TextBox3.Value + 1
ElseIf TextBox4.Value = TextBox1.Value Or TextBox4.Value = TextBox3.Value Or TextBox2.Value = TextBox4.Value _
Or TextBox4.Value = TextBox5.Value Or TextBox4.Value = TextBox6.Value Then
TextBox4.Value = TextBox4.Value + 1
ElseIf TextBox5.Value = TextBox1.Value Or TextBox5.Value = TextBox3.Value Or TextBox5.Value = TextBox4.Value _
Or TextBox2.Value = TextBox5.Value Or TextBox5.Value = TextBox6.Value Then
TextBox5.Value = TextBox5.Value + 1
ElseIf TextBox6.Value = TextBox1.Value Or TextBox6.Value = TextBox3.Value Or TextBox6.Value = TextBox4.Value _
Or TextBox6.Value = TextBox5.Value Or TextBox2.Value = TextBox6.Value Then
TextBox6.Value = TextBox6.Value + 1
End If




Set wks = Sheet1
Set addItem = wks.Range("A65356").End(xlUp).Offset(1, 0)
addItem.Offset(0, 0).Value = TextBoxl.Value
addItem.Offset(0, 1) = TextBox2.Value
addItem.Offset(0, 2) = TextBox3.Value
addItem.Offset(0, 3) = TextBox4.Value
addItem.Offset(0, 4) = TextBox5.Value
addItem.Offset(0, 5) = TextBox6.Value
End Sub

mancubus
11-16-2017, 06:12 AM
try changing TextBoxl.Value to TextBox1.Value

trebor58
11-16-2017, 06:24 AM
try changing TextBoxl.Value to TextBox1.Value

Thank you so much, I believe the saying is I could not see the wood for the trees, I have looked and looked and even looked again, and did not see, that simple error.