Excel Performance Problems

[Jeff]

All,



I am working on an Excel 2003 scoring tool for sales and professional
services. The approximate size of this file is 6MB, but it is largely
due to a number of controls embedded into Excel, not actual data.
There are about 40 worksheets, each with about 10 questions per sheet
and associated option buttons, text boxes, control bars, etc.
Performance slows down considerably after just 2 MB.



As an alternative, I broke the worksheet into smaller files of 3
worksheets each, and then call up a separate worksheet when a command
button is pressed and then close the worksheet I was just in. When I
do this, I receive an "Application-Defined or Object-Defined
Error". My goal is to significantly improve performance. Would any
of you have a few minutes to help me out with this?



Thanks,



Jeff

[dok112]

Are you using 1 book as a "call sheet" to call the other 3 books open? or have you set the commands into your excel directly? Also, what was the code you are using when the error msg comes up? When it happens and it prompts you to end or break, select break, and copy and paste the code you are using at the time of the error msg (usually in yellow). I'll look at it and see what I can make of it.

[Jeff]

I am using a macro behind a menu item in a custom-built toolbar to
launch the separate workbook. The code in the macro to launch the
second workbook is as follows:

Workbooks.Open Filename:= _
"d:\Marketing Effectiveness Quotient\MEQ_Workflow.xls"

Range("c11").Select

Then in the Open event in the second workbook, I try to close the first
workbook with this code:

Private Sub Workbook_Open()
Workbooks("EloquaMarketingEffectivenessQuotient.xls").Close

End Sub

It executes all the code, but when it is complete, I get the error
message.

Also, is there a way to close other workbooks without Excel sending
focus to the other workbooks before they close (I just want to stay in
one workbook while closing all others)

Thanks.

[dok112]

I can write a vba to close all the books except the one open without changing the focus. As far as the error goes, I would have to see the error when it's occuring to see where in the code it's breaking. If you want, you can send me the book and I will look at the code for any errors. if so, send me a private msg on here and I'll give you my email address to send it to.