"Object not found"

[jproffer]

Sub Auto_Lines()
On Error GoTo 1
Static counter As Long
Static count As Long
Static time As Integer
Static cb1 As Variant
Static cb2 As Variant
Static cb3 As Variant
Static cb4 As Variant
Static cb5 As Variant
Static cb6 As Variant
Static cb7 As Variant

If counter=0 Then
cb1 = UserForm2.CheckBox1.Value
cb2 = UserForm2.CheckBox2.Value
cb3 = UserForm2.CheckBox3.Value
cb4 = UserForm2.CheckBox4.Value
cb5 = UserForm2.CheckBox5.Value
cb6 = UserForm2.CheckBox6.Value
cb7 = UserForm2.CheckBox7.Value
count = UserForm2.TextBox2.Value
time = UserForm2.TextBox1.Value
Unload UserForm2
Goto 3
Else
3
NextTime = Now + (time / 1440) '1440
If cb1 = True Then
Call Pull_NFL_Football_Lines
End If
If cb2 = True Then
Call Pull_NBA_Basketball_Lines
End If
If cb3 = True Then
Call Pull_NCAA_Football_Lines
End If
If cb4 = True Then
Call Pull_NCAA_Basketball_Lines
End If
If cb5 = True Then
Call Pull_NHL_Hockey_Lines
End If
If cb6 = True Then
Call Pull_MLB_Lines
End If
If cb7 = True Then
Call Pull_NFL_Football_Lines
Call Pull_NBA_Basketball_Lines
Call Pull_NCAA_Football_Lines
Call Pull_NCAA_Basketball_Lines
Call Pull_NHL_Hockey_Lines
Call Pull_MLB_Lines
End If
counter = counter + 1
If counter >= count Then
counter = 0
count = 0
time = 0
cb1 = ""
cb2 = ""
cb3 = ""
cb4 = ""
cb5 = ""
cb6 = ""
cb7 = ""
MsgBox "Your Auto-Line session has ended normally.", vbOKOnly, "Auto-Lines Finished"
Exit Sub
Else
Application.OnTime NextTime, "Auto_Lines2"
End If
GoTo 2
1
MsgBox "An error has occured and your" & Chr(13) & "Auto-Line session has ended unexpectedly!", vbCritical, "Auto-Line Error"
2
End Sub


Sub Auto_Lines2()
Call Auto_Lines
End Sub

The above code is called by a command button on a userform. It's supposed to run a third code (or several others...one for each checkbox that is checked) to pull a table(s) online a certain number of times, at a certain interval.

As it is, it does all of that just fine. It gets thru the count at the desired interval, pulling each time, etc...anyhow, it does what it's supposed to....

BUT

When it gets to the end, I get the message that it's done, click ok and then the next time I try to open that userform to run it again, it bugs out "can't find the called object" or something close to that...AND, when I try to save the workbook I get the "crash" message...ya know, "restart, or search for a solution online and restart".

The way I take that is that the userform is not unloading right, and can't be reloaded. When it first did it, I thought it was because the UF was open the whole time (it was in a previous attempt), which is why I have all the static variables holding the UF control values.

Is there anything horribly wrong with the code that you can see?

Any thought are greatly appreciated.

Thanks in advance :)

[Paul_Hossler]

1. A sample WB would help
2. I assume that you have the colon after the Goto labels, but it did not paste in?



.....
GoTo 2
1: ' <============== colon
MsgBox "An error has occured and your" & Chr(13) & "Auto-Line session has ended unexpectedly!", vbCritical, "Auto-Line Error"
2: ' <============== colon
End Sub



3. Did you have Option Explicit at the beginning of the module so that all vabiables have to be defined?

Paul

[jproffer]

Option explicit is there now. The only variable not defined already was NextTime.

No colons, but it seems to go to the line labels correctly.

Trouble comes when I use it once, close it (or I can only assume the form is closed...I've told it to unload), then try to run it again. It bugs out "object not found" and on debugging it highlights the command button code "Userform2.Show"

I'll take out some of the unnecessary code for size reasons and post a workbook with the relevant code and form.

[jproffer]

Here's a sample WB. The only 2 menu items that will work are:

In the far left ribbon menu: "View NFL Betting Lines"

The far right ribbon button: "Set-Up Automatic Lines"

The rest are disabled and code removed for size reasons.

To test the automatic lines userform, use the far right ribbon button and (at least I was testing with) use 1 minute intervals and 2 as the number of calls. It does the same thing no matter what interval you use though.

I'm showing a red X on the attachment, but hopefully it goes through....here goes.


EDIT: BTW, the only checkbox that you should check when testing is the "NFL Football" one, as thats the only code thats left to pull.

Also, be prepared for a crash. I hope that it don't happen, but have your stuff saved before messing with this because it might crash your excel.

[rbrhodes]

Hi jp,


Worked fine for me _except_ these lines errored out as type mismatch:


Sub Auto_Lines()
'On Error GoTo 1
Static counter As Long
Static count As Long
Static time As Integer
Static cb1 As Variant
Static cb2 As Variant
Static cb3 As Variant
Static cb4 As Variant
Static cb5 As Variant
Static cb6 As Variant
Static cb7 As Variant
Dim NextTime As Variant
cb1 = UserForm2.CheckBox1.Value
cb2 = UserForm2.CheckBox2.Value
cb3 = UserForm2.CheckBox3.Value
cb4 = UserForm2.CheckBox4.Value
cb5 = UserForm2.CheckBox5.Value
cb6 = UserForm2.CheckBox6.Value
cb7 = UserForm2.CheckBox7.Value

'//Errored out as value is ""

If UserForm2.TextBox2.Value <> "" Then
count = UserForm2.TextBox2.Value
End If

'//Errored out as value is ""

If UserForm2.TextBox1.Value <> "" Then
time = UserForm2.TextBox1.Value
End If

Unload UserForm2

<snip>

NextTime = Now + (time / 1440) '1440

[Paul_Hossler]

1. I REALLY would not use Excel/VBA keywords like time and count for variables. I chagned then to Xtime qnd Xcount


2. The TextBoxs return a string of length 0 ( i.e. ""), so you need to test for that:


If UserForm2.TextBox2.Value = "" Then
Xcount = 0
Else
Xcount = CLng(UserForm2.TextBox2.Value)
End If
If UserForm2.TextBox1.Value = "" Then
Xtime = 0
Else
Xtime = CLng(UserForm2.TextBox1.Value)
End If



3. I had to edit your CustonUI XML because I did not have a TabPDF, but I doubt that that made any difference

After the above, I could run it twice with a normal exit after the second run

Paul

[mikerickson]

With all those GoTos, somehow the fact that "If counter=0 Then" has an "Else" but no "End If" is going unnoticed.

[Paul_Hossler]

May be style / personal preferance, but I really don't like to use a 'blanket' On Error Resume Next. It just allows error to be ignored until something downstream happens, and then it's really hard to debug ...


Sub Pull_NFL_Football_Lines()
On Error Resume Next
<etc.>


Sub Auto_Lines()
On Error GoTo 1
Static counter As Long
<etc.>




If there's a potential error condition on a statement, I like to test for it, and then handle it, possibly bracketing only a group of statements with On Error Resume Next / On Error Goto 0

Fragment --


Set r2 = Nothing
On Error Resume Next
' might not be and CellTypes in r1
Set r2 = r1.SpecialCells(CellTypes)
On Error GoTo 0

If Not r2 Is Nothing Then
<more code here>
End If


I will have a general purpose Error Handle available some times, but still bracket as in the fragment and use On Error Goto ErrHandle insteead of the Goto 0

Paul

[jproffer]

I appreciate all the help guys...I truly do.

I will put some of these suggestions (about all the goto's and generic error handlers) into practice...on OTHER procedures, lol...I think I'm done messing with this one. If they want to pull the lines more than once, they can use the manual one (the left menu) multiple times.

It would be nice if I could get it to work, but I've tried different things and...well, I can't. It's not a huge loss.

Again though, thank you all for the general suggestions and such.