May be worth a try but this will take me quite some time to accomplish for
each of the workbooks!!

One nasty bit I can think of are named ranges....I have loads of named
ranges in my workbook. Is there a way to copy the named ranges across
without them still referencing the old workbook that I am copying from? And
if I am copying the cells from one worksheet at a time these will often
refernce named ranges in other worksheets that do not yet reside in the new
workbook becuase I havent copied them yet! Not to mention the dynamic ranges
that dont appear in the worksheets themselves!!

I am thinking that as the problem seems to dissapear when the file is saved
first, is there maybe some issue with the user saving from different PCs that
may have actually caused this? and that it gets corrected when it is saved by
the machine that then has it open?!

"Peter T" wrote:

I don't have a clue as to what's wrong with your workbook or project. But it
might be worthwhile to rebuild a totally new wb from scratch. Don't copy
sheets but should be OK to copy cells.

Drag modules between projects, copy/paste any code in thisworkbook or sheet
modules.

Regards,
Peter T


"Peter" wrote in message
...
Futher to the below, I have noticed that simply saving the workbook before
attempting to run any macros also seems to 'uncorrupt' the workbook so
that
the next time it is opened it does not crash when using macros? maybe I
should set the workbook to save on opening (if not opened read only)??
This
of course would still leave the workbook liable to crash when opened as
read
only!

"Peter" wrote:

I have an Excel 2002 workbook that intermittently gets the above error
message ("Excel 2002 has encountered a problem and needs to close") and
corrupts.

The workbook contains class modules, standard modules and userforms.
Most
frequently the error message occurs when opening the workbook and
clicking a
button on the userform that opens on entry (this button simply says
"exit"
and unloads the form). This isn't always the cause and sometimes may
trigger
when updating links, or clicking on another button outside if the
userform.
The spreadsheet is not shared, and is used my many users across a
network (ie
only one person can have write access at any one time).

If I run Rob Bovey's Code Cleaner against this workbook (once it has
been
corrupted) on entering (before clicking any buttons) then it resolves
the
problem. However a few days/weeks/months later the problem may re-occur
despite the fact that no new vba code has been added or edited. I was
under
the impression that the code cleaner only cleans stuff from vba memory
that
accumulates when new code is added? If this is true then how does it
resolve
my problem if nothing has since accumulated (because I have added no new
code)?!

Is there some way for me to identify what the root cause for the problem
is?
Does stuff accumulate in memory if perhaps I have not set variables to
nothing at the end of procedures for example?

Is there a way for me to run this code cleaner against a spreadsheet
automatically every time the spreadsheet is opened? The spreadsheet is
very
critical and doesn't inspire user confidence if it crashes - this is the
only
way I can think of to make sure the spreadsheet doesn't crash?!

HELP!