Why does this simple Sub fail?

G

Guest

The following is all the code in ThisWorkbook
Option Explicit

Private Sub WorkBook_Open()
Worksheets("LB").Range("A1:E1000").Clear 'Fail 1
CurrentEntry = -1
Worksheets("Main").Select 'Fail 2
Srch.Show
End Sub

This code fails with the message:
Run time error '57121':
Application-defined or object-defined error

The failure occurs on the line marked 'Fail 1
If I move the execution point to the next line and press F5 it fails with
the same message at the line marked 'Fail 2
If, at any point, I stop execution and re-execute the sub (by pressing F5)
it runs fine.

Worksheet "LB" exists and is hidden
Worksheet "Main" exists, is not hidden and is on top at open. (The select
statement is just incase the workbook was stopped with another worksheet
displayed.)

Remember this Sub works fine if I do nothing but stop and restart
execution.

Thanks very much for any help.
 
G

Guest

Thanks for your reply. If that were the case it would not compile, but it
does compile ang gets an execution time error. As mentioned, it works
perfectly if I simply stop execution and restart it. Also it does not fail
on that line.

That variable is defined elsewhere as a public variable.

By the way, I am using the Excel in Office 2003
 
G

Guest

Thanks. It looks like a lot of people have run into similar problems with
workbook open events. NOTE: This code was written and runs on Windows XP
- no Macs involved.

Still trying to resolve this problem.
 
G

Guest

I replaced the offending code as follows:
Option Explicit

Private Sub WorkBook_Open()
Call OhNo
End Sub

Sub OhNo()
Worksheets("LB").Range("A1:E1000").Clear
CurrentEntry = -1
Worksheets("Main").Select
Srch.Show
End Sub

Note: All I did was to move the code from the Workbook open event to
another sub and call that sub from the Workbook open event.
 
Top