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]

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

The Object is a commandbutton located on sheet1. It is one of 15 or 20 other buttons that have no trouble loading. Unless of course once it stumbles on this button it quits checking for any other problems.

 

[chuckchuckit]

What does that commandbutton do?

Can you post it's code?

 

[MPW]

The code that the button invokes should not be an issue because it is only run when the button is clicked by the end user.

The issue (I believe) is that I am trying to adjust it's size and position when the workbook is opened. I think Andrew is on the right path in trying to get the workbook to load it's elements before running the sub to change the buttons. Perhaps a different type of ontime event might be needed?

A little history: The reason for adjusting the size and position of the buttons on sheet1 goes back to a old glitch in excel. Basically if you are using 2 different sized cloned monitors the buttons can begin to shrink or grow or their text can start to shrink into nothing. The only way that I have ever found to combat this bug is to refresh the size of the buttons when ever I run one of the macros on this sheet.

If you still would like to see the code let me know. It is pretty involved though because it calls on other subs etc. and as I said I don't believe it is the issue.

 

[chuckchuckit]

You may be correct there. But what is coming to mind is that your first post about it not being able to access an object is similar to my DDE example. When you say cloned monitors that is starting to sound like it is interfacing the operating system, so a running macro may indeed be interfering with your operating system handling the monitor situation while the macro is running.

Have you tried stepping through the code with F8 to see where it crashes? If you can get the line it crashes on, perhaps you can post the code that is involved with that.

 

[MPW]

The the place in the code that caused the debug in the beginning of my post was here:

   Sub ResetButns()
    'Changes the size of all buttons up in size and then back to the original size.
    
    'PASTE TEXT Button
    Sheets("Script").CommandButton1.Top = 96.75   '<------------DEBUG HAPPENED HERE
    Sheets("Script").CommandButton1.Height = 24.5
    Sheets("Script").CommandButton1.Height = 24.75
    Sheets("Script").CommandButton1.Width = 52.5
    Sheets("Script").CommandButton1.Width = 52.75
    
    'STATS Button
    Sheets("Script").CommandButton1_PROGRESS.Top = 51
    Sheets("Script").CommandButton1_PROGRESS.Height = 19.25
    Sheets("Script").CommandButton1_PROGRESS.Height = 19.5
    Sheets("Script").CommandButton1_PROGRESS.Width = 52.5
    Sheets("Script").CommandButton1_PROGRESS.Width = 52.75

However, after Andrew suggested:

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

I wrote back to him with ths:

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

"Can't exit design mode because Control 'Sheets("Script").CommandButton1_PROGRESS' can not be created"

As you can see from the message excel didn't like the 'CommandButton1_PROGRESS' button, but it did pass over the almost identacle commands for 'Sheets("Script").CommandButton1'.


As far as the F8 question I can run this and any other procedure once the workbook is opened without any problem. It seems to only be when the workbook is opening that it has any problems.

Also the problem with buttons being resized is a known issue with Excel. One of the buttons that would cause the bug to manifest was a simple find next macro tied to a button.

I hope this might clear up you questions.

 

[chuckchuckit]

It does seem like it is not seeing one of the buttons. Not sure I understand your statement: "It seems to only be when the workbook is opening that it has any problems."

Does it error on the workbook opening line of code or on the resizing line(s)? Is the button run sometimes "without" opening a workbook?

You might try putting following line of code before and after suspected lines of code causing issues:

DoEvents

This has the processor catch up to itself before doing any next lines of code. Having a bunch does not seem to slow things down much, so you can remove non-needed ones later if it works.

 

[MPW]

 Private Sub Workbook_open()
    OpenCORE           'Located in Module 1.
End Sub

This opens

Sub OpenCORE()
    
    Application.ScreenUpdating = False
    Lockup
    MakeWork
    ResetButns           'Located in Module 1.

Lockup - protects some of the workbook.
MakeWork - enables events and calculation (not really needed since that happens when ever a workbook is opened)

ResetButns - is the code I shared with you earlier.

Sub ResetButns()
    
    
    'Changes the size of all buttons up in size and then back to the original size.
    
    'PASTE TEXT Button
    Sheets("Script").CommandButton1.Top = 96.75
    Sheets("Script").CommandButton1.Height = 24.5
    Sheets("Script").CommandButton1.Height = 24.75
    Sheets("Script").CommandButton1.Width = 52.5
    Sheets("Script").CommandButton1.Width = 52.75
    
    'STATS Button
    Sheets("Script").CommandButton1_PROGRESS.Top = 51
    Sheets("Script").CommandButton1_PROGRESS.Height = 19.25
    Sheets("Script").CommandButton1_PROGRESS.Height = 19.5
    Sheets("Script").CommandButton1_PROGRESS.Width = 52.5
    Sheets("Script").CommandButton1_PROGRESS.Width = 52.75

I will look in to using more doevent commands first chance I get tomorrow.

Thanks for the ideas.

 

[chuckchuckit]

I wrote below response before reading your above one:
============
I reread your #1 post. This line:

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

When you "End" the debugger, this also "Ends" all macros including any "Worksheet_Change" macros or events etc. If it then works OK now that all macro's are completely stopped, I am still suspecting possible problems from a macro that is still running somewhere in your excel program. More slight clues keep pointing in that direction.

I would keep trying to isolate "when it works, and when it does not work". Am thinking there might be an auto macro running somewhere (Worksheet perhaps).

 

[chuckchuckit]

Your OpenCORE sub is still running when you are resizing your buttons. This might be the problem. I would try assigning the OnTime call to "ResetButns" instead, and then be sure to exit the main macro before the "ResetButns" starts 9 seconds later (9 seconds for testing).

The code gets a little complicated as you have to then call your main program back again after the button resize is done, but if you want to try that I can help with that tomorrow as I have to run off now too.

 

[Andrew Poulsom]

Have you tried code like this to position and size the CommanButtons:

    With Sheets("Script").OLEObjects("CommandButton1_PROGRESS")
        .Top = 51
        .Height = 19.25
        .Height = 19.5
        .Width = 52.75
    End With