Macro-Driven Workbook.Close Sub Crashes Excel 2010

[ChuckRobert]

I have a series of linked workbooks, initially built with Excel 2003. After converting to Office 2010 I saved them as .xlsm workbooks, and updated all links. Now, I am having a problem with a workbook close macro that never gave me a problem while in Excel 2003. I have tried 3 variations of closing the workbook with a macro assigned button, but the problem remains with all of them:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Specifically - <o></o>
1. If I only have one workbook open, I can run the workbook.close macro button all day without failure. <o></o>
2. If I have more than 1 workbook open, and click the X in the upper right corner to close a workbook, the workbook closes fine. <o></o>
3. However, if I have more than 1 workbook open, and click the workbook.close macro button to close a workbook, about 50 percent of the time I get the following error message:<o></o>
<o></o>
“Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience. The information you were working on might be lost. Microsoft Excel can try to recover it for you.” Then a check box displays to recover my work and restart Microsoft Excel. <o></o>
<o></o>
I have tried the 3 following variations to the workbook.close macro, but all provide the same results. <o></o>

[SIZE=3][FONT=Times New Roman]Sub SaveAndCloseWorkbook()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]ActiveWorkbook.Close True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End Sub<o:p></o:p>[/FONT][/SIZE]

[SIZE=3][FONT=Times New Roman]Sub SaveAndCloseWorkbook()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]ThisWorkbook.Close savechanges:=True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End Sub<o:p></o:p>[/FONT][/SIZE]

[SIZE=3][FONT=Times New Roman]Sub SaveAndCloseWorkbook()<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim wbName As String<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]wbName = ThisWorkbook.Name<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Workbooks(wbName).Close savechanges:=True<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End Sub<o:p></o:p>[/FONT][/SIZE]

I’ve looked extensively, but have been unable to determine what is causing the error. Any suggestions would be greatly appreciated. <o></o>

 

[Smitty]

There's nothing wrong with the code, so it could just be a bad install, or a corrupt workbook.

Do you have anything in the Before_Close event that could be causing problems?

What happens if you open a copy in 97-2003 format and don't save as .xlsm?

 

[ChuckRobert]

Thanks for the response. There is nothing in the Before_Close event, and everything worked perfectly in Excel 2003. The Office 2010 install was implemented through our network this week. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Smitty]

There really shouldn't be any forward compatibility issues, so that is odd, but it happens. Can you restore a backup of the workbooks, and them in the 97-2003 format, but don't save as 2010? Things like this can be a real pain to track down. You might want to check your references in the VBE for anything missing. For instance, did you possibly have an add-in that the workbooks reference, but haven't loaded it yet?

 

[grazer]

Hello all,

By coincidence, I have encountered a very similar problem.

I am not a VBA programmer by any stretch of the imagination - so excuse any lack of detail.....

We have an Excel sheet used to to track Attendance, and the person who coded it has long since left the firm. I have had to update the file to handle new holiday rules, etc, and tool the opportunity to save as Excel 2007 format (this was necessary as I use an extended IF statement which only 2007 can handle)

There is an 'Enter / Exit' title screen, and on the exit route there is a

workbooks.close

command. If this is left in, Excel gives the same message as described by ChuckRobert below :

“Microsoft Excel has encountered a problem and needs to close. We are sorry for the inconvenience. The information you were working on might be lost. Microsoft Excel can try to recover it for you.” Then a check box displays to recover my work and restart Microsoft Excel. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

If the command is commented-out, the user is prompted to save the file. Unfortunately this causes other issues.

I can see what you mean about it being difficult to troubleshoot, but there does appear to be an issue with 2007 handling this.

 

[ChuckRobert]

Thanks for the feedback! The only VBE reference I deleted was one for Calendar Control, which was not needed anymore (or offered in the base 2010 install). I'll look to see if I have any imbedded objects for that reference...

I'm starting to think that there may be an issue with the install, as I noticed a few other anomolies, such as:

1. If I drag a group of cell formatting down, then select the entire section, and change the interior line thickness, only some will change.

2. I have cells that use conditional formatting to change color if 2 cells contain the same values. With 2010, only 1 cell will change color if the condition is activated. The second will not change color until I scroll that cell off the screen, then back on. Likewise, if I clear a conditional format condition...

3. When copying/pasting (or dragging cell formulas) for conditional formatting formulas that contain a correctly formatted mix of standard and absoulute references, none of the non-fixed values will update.

None of these issues ever gave me any problem with 2003.

 

[bwaights]

Well first time on this forum. but i think i may have found an answer to my (very similar problem) of Excel crashing with the use of Workbook.Close

My situation :
i had stolen code from a 2003 workbook and used it on a 2010 workbook. Every time the code hit Workbook.Close , it would crash , but instantly , and without any messages.

Anyway. The solution i found is to use the code "Application.Windows("filename").Close FALSE (or True) "

as opposed to the "Workbook" base command.

We have had many library issues with upgrading our 2003 code to 2010 , so the issue may stem from there , but couldn't tell you for sure. Seem to work fine now.

 

[ChuckRobert]

Welcome to the forum, and thanks for the feedback! I ended up making new 2010 programs, which of course works just fine.

 

[supdawg]

Well first time on this forum. but i think i may have found an answer to my (very similar problem) of Excel crashing with the use of Workbook.Close

My situation :
i had stolen code from a 2003 workbook and used it on a 2010 workbook. Every time the code hit Workbook.Close , it would crash , but instantly , and without any messages.

Anyway. The solution i found is to use the code "Application.Windows("filename").Close FALSE (or True) "

as opposed to the "Workbook" base command.

We have had many library issues with upgrading our 2003 code to 2010 , so the issue may stem from there , but couldn't tell you for sure. Seem to work fine now.

I am having a similar issue, and I would like to try this, but hard coding a workbook's name into the code I don't want to do.

 

[IsItFridayYet]

try

ActiveWorkbook.Close (False)     'nosave

oops missed the part about 2010...works on 2003