Run-time error 32809 when workbook opens

[MPW]

Hi All,

Using Office 2003.

I have a workbook which runs great but occasionally I get a debug during with the workbook_open sub. Specifically when it is dealing with any objects in the workbook.

The kicker is if I "End" the debugger, and then go to the workbook_open sub to run it, it works normally.

Until today this was a sporadic problem. Today it has happened every time I opened the workbook.

It almost seems to be trying to deal with the objects before the objects are loaded. Should I put a delay on the code to give the workbook a minute to collect it's thoughts?

Thanks for any help.

MPW

 

[MPW]

Mr. Andrew, I made the change with the 'with' statements. (It is better code, but if I take out the ontime statement it still gives the debug.)
Are there any benefits to using a 'with' statement rather than a straight command (like the one I was using) other than how it looks?


chuckchuckit, I tried moving the ontime statement to before the "ResetButns" and it did clear that routine but then gave the same debug on another 'If' statement further on in the OpenCORE sub.

If Sheet("Script").Visible = True Then

Again choking on an object, this time the sheet itself.


I wonder if there is a command to force all the objects to load before running any code. Kindof like a DoEvents command but aimed at the workbook or sheet.

 

[chuckchuckit]

Am still suspecting that your macro running is interfering somehow with loading objects etc. Try this code that completely ends your macro from running to give time for the loading(s) to occur. Put these 3 below in a regular Module:

Sub OpenCORE() 
      'Application.ScreenUpdating = False
 
      Lockup
      MakeWork
      Application.OnTime Now + TimeValue("00:00:00"), "TheButnsProcedure" 'Runs function immediately after OpenCore quits.

 End Sub

Function TheButnsProcedure ()
     Application.OnTime Now + TimeValue("00:00:09"), "YourMainProgramAfterTheButnsProcedure" 'Gives 9 seconds before main program will run.
     ResetButns 'Has 9 seconds to load before running your main program function below.
End Function

Function "YourMainProgramAfterTheButnsProcedure" ()
     'Do your error checking of loading the buttons etc here and if not loaded then call this function again using OnTime, then exit this function.
     'If passed error checking

          'Do all your code that will occur after your buttons would normally be loaded OK.

End Function

If this loads everything ok, then you can adjust the 9 seconds lower, or do above suggested error checking on your buttons/objects to see if they are there before continuing running code in "YourMainProgramAfterTheButnsProcedure".

 

[MPW]

The OpenCORE sub runs around a dozen other subs.
It also modifies the Commandbars and some other things as well.

It is half way through this sub when it gets the debug.

Are you suggesting I break it into multiple subs with a delay after each of the other sub it runs?

 

[chuckchuckit]

MPW, Gotta leave for the day, but in the above code you may have to "adjust your code" in your function "ResetButns" so that you do not actually try to adjust the button sizes or other such things until you know those objects are loaded OK 1st. That is what the 9 seconds delay is for. And during that 9 seconds is when none of your macros should be running.

You will have to determine where in your code that you make the call to load the object, "but not change them yet for those 9 seconds". If you can do that, then I think you can get it to work.
========
I just saw your above post. Yes, I am suggesting that you do break up your code into different sections to try to see where the delay is needed in those many subs. I am suspecting you have a call somewhere that is loading an object etc that is taking more time to complete than is obvious. The above code will help you determine where that is occuring and where to then set up the appropriate time delay so it can load OK first before the rest of the code then runs.

 

[MPW]

OK, So I remmed out everything in the OpenCORE sub before

If Sheet("Script").Visible = True Then

So when the workbook opens it runs this

Private Sub Workbook_open()
     OpenCORE
End Sub

This opens

Sub OpenCORE()
    If Sheet("Script").Visible = True Then

It still crashes on this line without running anything else before it. If I drag the step Into arrow past the crash and try to step through it, every line I try to step into that has any reference to the sheet will result in a object orientated debug.

However if I stop the code and run the

Private Sub Workbook_open()
     OpenCORE
End Sub

It will run fine without a hitch.

So this is telling me that the problem might be in the workbook or the sheets themselves and not the code I am attempting to run.

 

[MPW]

After I posted the last one I was browsing the Forum and found this.
Macro fails to close open workbook. posted by wwbwb

It sparked me to thinking about my problem of the workbook not loading so I added this

Public wb1 as Object     'ADDED THIS TO Module1

Private Sub Workbook_open()
     Set wb1 = ActiveWorkBook     'ADDED THIS
     OpenCORE
End Sub

It nowl runs fine. I am guessing that setting wb1 forces the workbook to fully load itself.
In any event it does work. If any can explain why that would be great.

 

[theaudioguy]

Wow, great job.

 

[chuckchuckit]

Glad you got it fixed. When one keeps shaking the apple tree, sooner or later the right apple may show up!

Thanks for reporting your results.