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

 

[theaudioguy]

What specifically does the debug say?

 

[Andrew Poulsom]

Try moving the code in your Workbook_Open event procedure to a procedure in a general module, and call it using the OnTime method with a suitable delay.

 

[chuckchuckit]

There is a confirmed bug in MS where if you are starting any macro with a quick key combination using the "Shift" key, you will get "random" errors on the line that opens a workbook.

If so, try Ctrl-R instead of Ctrl-Shift-R for instance to start your macro.

 

[MPW]

to the theaudioguy,
I tried to post a screen shot of the Debug, couldn't quite work that out.

Here is the message:
run-time error '32809':
Application-defined or object-defined error.

To Andrew Poulsom,
Long ago I moved all of the workbook_open routine to a separate module and I call that from the workbook_open sub.

To chuckchuckit,
All of my macros are either event based or button driven. None of them use keyboard shortcuts.


To add a little to the intrigue I modified the spreadsheet after saving it to a new name and it has been working without a hitch. . . so far.
However 2 separate versions of the original workbook still will crash on open.
I cannot help but think that the problem is still lurking about.
I hate sporadic problems. I am open to exploring anything at this point.

Thanks for the questions.

 

[Andrew Poulsom]

How are you calling it? Try something like:

Application.OnTime Now + TimeValue("00:00:01"), "YourProcedure"

 

[MPW]

Thank you for that. I am closer then I was.

It got past the run-time error but I did get this alert box:

"Can't exit design mode because Control 'CommandButton1_PROGRESS' can not be created"


CommandButton1_PROGRESS was one of the first buttons created in this workbook. My boss named it and he likes long names . It has been that way for around 10 years. Do you think that the length of the name could be the problem?

The command button changes shape and position when the workbook is opened.

Once I click "OK" on the alert, the workbook loads normally.

 

[Andrew Poulsom]

Maybe you need to increase the delay of 1 second in my code.

 

[MPW]

I increased it to ("00:00:09") with the same results.

 

[chuckchuckit]

Perhaps your particular "object" (I am suspecting it is an external object) is not being loaded or updated properly while your macro is still running. So your code is trying to access an object not there yet. This happens for instance when using DDE cell updates. Many DDE cells results will not load into the cells at all, until the macro has completely quite running.

If this is the case, then using Andrew's code:

Application.OnTime Now + TimeValue("00:00:09"), "YourProcedure"

with 9 seconds delay there should be long enough to load your object, as long as the macro that called the above OnTime code has completely quit. This gives 9 seconds that no macro's are running, before "YourProcedure" starts.

If this works, then you can lower the 9 seconds time, or use an error check every second or so to see if your object is there yet (to avoid using a full 9 seconds if it really loaded OK in 2 seconds).

I use this often with gathering DDE data using the OnTime procedures.