Ken:

try to stick to data validation and excel's builtin controls from the
forms toolbar as opposed to the embedded contols from the control box
toolbar.

Apparently you also create controls "on the fly"?

I think that stability improves if you work with an existing control and
not delete/create controls over and over again.

As you may have noticed Excel doesn't renumber some of the collection
indexes for these objects. You'll be inserting Shape 4567 before you
know it... During development and testing I've seen totally empty books
(1 module, 1 sheet, 100 cells in usedrange) which when saved where 1
Megabyte or more.


Ideas for repairing:

Also note the VBA cleaner just cleans out code modules and userforms.
Then saves an uncompiled copy of it. It works very effectively on
userforms, but cannot clean a worksheet.

I have seen but never used some workbook repair utilities. Maybe those
can be effective. Since the number of sheets isn't that high yuo may
well do it manually.

create a new workbook and carefully copy over the cells to new sheets.
Then copy the objects copy the code. You cant use copy sheet in this
case as you'll copy over the errors.



I'd be happy to have a look at the thing. Zip and mail pls.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Ken Dahlberg) wrote:

keepitcool,

I see what you're talking about. I deleted several thousand files
from this temp directory, including many that looked like my embedded
controls, multiplied by several working copies of the workbook. I
expected, then, to be able to add the extra controls I needed. But no
such luck. The workbook still dies the same death.

Unfortunately my client wants controls embedded on his worksheets
instead of in userforms. If there is no workaround for this then I
will just have to educate him.

Thanks for the insight.... any other ideas??
Ken

keepitcool wrote in message
. ..
excel makes extensive use of the temp directory for displaying
embedded controls.

excel is known to crash if the temp directory contains many files.

in other words:

either force regular cleaning of the temp directory
dont use embedded controls


Open the temp dir in the users profile in explorer.
delete all files and directories. (some are locked by windows.)

start excel, open your book:=

now have a look at that tmp directory.
see all those emf and tmp files?
NOW you know what i avoid embedded controls like the plague.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Ken Dahlberg) wrote:

EXCEL 2002. This is a follow-up to another post of mine that
hasn't appeared yet: "Whose error: Mine or Excel's?". That was
about the same problem described here, but some of the information
given there is probably irrelevant.

I'm not really sure what is relevant. The workbook in question has
7 visible worksheets and 3 hidden worksheets. There are about 70
embedded controls spread over 3 of the worksheets that also contain
one embedded chart each. The size of the .xls file is about 1 MB.
So it's not a huge project.

OK, here's the problem. I add a new CommandButton control to one
of the worksheets that already has a chart and 21 other controls.
I don't do anything else - no code added behind the button. I save
the workbook, close it, and open it again. Now, I get the dreaded
box that says "Microsoft Excel has encountered a problem and needs
to close. We are sorry for the inconvenience". And if I follow the
option to "Recover my work and restart Excel", it opens the file
with ALL the VBA code stripped out. This happens even if I disable
macros on startup.

I can add a CommandButton and even a couple of extra OptionButtons
to either of the other two worksheets containing charts and
controls, and the project survives. So there's something about the
particular sheet I'm trying to change. But I'm clueless what else
to try, or what information to provide here, unless someone knows
enough about this kind of problem to ask the right question.....???

Thanks,
Ken Dahlberg