Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code Clea
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code Clea
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code Clea
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code
Hi Darren,
I am relatively confident the line ws.Cells.Copy .Worksheets(i).Cells should work fine, even for quite complex areas of merged cells. But please post back if you find it doesn't copy all correctly. Notwithstanding, I should emphasize the macro was quickly put together, lightly tested and as I said "for ideas". So you will certainly need to check the integrity of the new workbook. The code would need extending to make similar Worksheet controls (they won't be copied), any macro onaction strings in buttons/shapes linking to own workbook would need editing. Regards, Peter T "Darren Hill" wrote in message ... This macro you've posted could be a lifesaver for me, as I'm having a very similar problem to the original poster, but only with one workbook. Will this line: ws.Cells.Copy .Worksheets(i).Cells work for merged cells? If not, what code would I need for that? Thanks, Darren Peter T wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code
That's good to hear. Regarding the quickly put together-ness of the
macro, don't worry, I won't blame you when it all goes pear-shaped :) My workbook relies on activex controls on forms, rather than worksheet controls, shapes, and onaction events, so I should be okay on that front. Thanks, Darren Peter T wrote: Hi Darren, I am relatively confident the line ws.Cells.Copy .Worksheets(i).Cells should work fine, even for quite complex areas of merged cells. But please post back if you find it doesn't copy all correctly. Notwithstanding, I should emphasize the macro was quickly put together, lightly tested and as I said "for ideas". So you will certainly need to check the integrity of the new workbook. The code would need extending to make similar Worksheet controls (they won't be copied), any macro onaction strings in buttons/shapes linking to own workbook would need editing. Regards, Peter T "Darren Hill" wrote in message ... This macro you've posted could be a lifesaver for me, as I'm having a very similar problem to the original poster, but only with one workbook. Will this line: ws.Cells.Copy .Worksheets(i).Cells work for merged cells? If not, what code would I need for that? Thanks, Darren Peter T wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2002 has encountered a problem and needs to close /Code
Thanks for the suggestions Peter T.
When I have time to check the newly created workbook is exactly in synch with the old one then I will see how that workbook behaves itself. The trouble in testing the success of this unforunately stems from the fact that I do not know the root cause and wont be able to say it's a success until it has run over a few months without corrupting! (as it infrequently happens). Although I may of course be able to say that it wasnt successful before that!! If it doesnt work then I may be forced to set the workbook to save on opening as that seems to rectify whatever problem Excel has. Regards, Peter "Peter T" wrote: Hi Darren, I am relatively confident the line ws.Cells.Copy .Worksheets(i).Cells should work fine, even for quite complex areas of merged cells. But please post back if you find it doesn't copy all correctly. Notwithstanding, I should emphasize the macro was quickly put together, lightly tested and as I said "for ideas". So you will certainly need to check the integrity of the new workbook. The code would need extending to make similar Worksheet controls (they won't be copied), any macro onaction strings in buttons/shapes linking to own workbook would need editing. Regards, Peter T "Darren Hill" wrote in message ... This macro you've posted could be a lifesaver for me, as I'm having a very similar problem to the original poster, but only with one workbook. Will this line: ws.Cells.Copy .Worksheets(i).Cells work for merged cells? If not, what code would I need for that? Thanks, Darren Peter T wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Microsoft Excel has encountered a problem and needs to close...... | Excel Discussion (Misc queries) | |||
Excel 2007 encountered a problem and needs to close | Excel Discussion (Misc queries) | |||
Microsoft Excel encountered a problem and needs to close | Excel Discussion (Misc queries) | |||
Microsoft Excel has encountered a problem and needs to close. HELP! | Excel Discussion (Misc queries) | |||
XL "encountered a problem & needs to close" | Excel Programming |