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

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

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

Does switching from ActiveX to Forms button(s) help at all?


"Ken Dahlberg" wrote in message
om...
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

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

KeepItCool,

Several people have suggested using Forms controls instead of Controls
toolbox controls. I'll give this a try. And I'll try cleaning the
particular worksheet where the problem occurs, following your
description.

I don't much want to have someone looking into the guts of this
particular item because it isn't a pretty sight. There are plenty of
things in there that need straightening out, that I don't have time to
mess with in this phase of the project. It is most generous of you to
offer to look at it.

Regards,
Ken

keepitcool wrote in message . ..
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.


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

Ken,

I have a similar problem with a rather large workbook that I use.
For some unknown reason, whenever I add a control to certain
UserForms, I get that same dreaded message.

One thing that always helps. I use it sporadically on other
workbooks but always use it on this one is Rob Bovey's
"Code Cleaner". It works for me every time.

You can get a copy of it he
http://www.appspro.com/

John

"Ken Dahlberg" wrote in message
m...
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

John,
I use the "Code Cleaner" religiously, but it's not doing it for me in this case.
Thanks,
Ken

"John Wilson" wrote in message ...
Ken,

I have a similar problem with a rather large workbook that I use.
For some unknown reason, whenever I add a control to certain
UserForms, I get that same dreaded message.

One thing that always helps. I use it sporadically on other
workbooks but always use it on this one is Rob Bovey's
"Code Cleaner". It works for me every time.

You can get a copy of it he
http://www.appspro.com/

John