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 isnt 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 Boveys 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 doesnt inspire user confidence if it crashes - this is the only
way I can think of to make sure the spreadsheet doesnt crash?!

HELP!

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 isnt 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 Boveys 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 doesnt inspire user confidence if it crashes - this is the only
way I can think of to make sure the spreadsheet doesnt crash?!

HELP!

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!

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!

Yikes, you have same problem with all your workbooks!

I have no idea if building a new wb will solve but from what you described
I'd be tempted to try that, at least with the most troublesome wb.

For your sheets names & cells some ideas in the following -

Sub test()
Dim i As Long
Dim ws As Worksheet
Dim wbOrig As Workbook
Dim wbNew As Workbook
Dim nm As Name
Dim nmNew
Dim wsNew As Worksheet

Application.Calculation = xlCalculationManual
Set wbOrig = ThisWorkbook
Application.SheetsInNewWorkbook = 1
Set wbNew = Workbooks.Add
Application.SheetsInNewWorkbook = 3

For Each ws In wbOrig.Worksheets
i = i + 1
If i = 1 Then
wbNew.Worksheets(1).Name = ws.Name
Else
wbNew.Worksheets.Add(after:=wbNew.Worksheets(i - 1)).Name = ws.Name
End If
Next

With wbNew.Names
' if not 100% sure the nm.RefersTo string is less than 255
' don't use this (could be a lot more work involved)
For Each nm In wbOrig.Names
..Add nm.Name, nm.RefersTo
Next
End With

Application.DisplayAlerts = False
i = 0
With wbNew
For Each ws In wbOrig.Worksheets
i = i + 1
ws.Cells.Copy .Worksheets(i).Cells
Next
End With

Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

Regards,
Peter T


"Peter" wrote in message
...
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!