Excel crash when workbook.close called from button

[MsgBox]

Using Excel 2007

I have a complex application written in vba where I would like the user to close from a button on the main dashboard. However, every time this button is pressed, excel crashes, displaying an error box:

"Microsoft Office Excel has encountered a problem and needs to close..."

I began stripping pieces from my VBA project and spreasheet one by one (userforms, controls, formatting) until literally the only thing I had left was a blank sheet with the button linked to a simple macro:

Please Login or Register  to view this content.

Still, excel would crash when clicking the button. Interestingly enough, when I would run the macro manually, without using the button, it worked without a hiccup. Indeed, when I went back to my original program with all of the code intact, running the macro from inside VBA did not result in a crash.

What's wrong here?

Thanks.

[Domski]

Hi,

Could be your vba project has become corrupted. You might want to try running a code cleaner through it:

http://www.appspro.com/Utilities/CodeCleaner.htm

Dom

[MsgBox]

Thanks Domski, I will try that and report back.

[snb]

Please Login or Register  to view this content.

This causes an error if the workbook hasn't been saved yet (and be given a path & name).

[MsgBox]

Ok, sorry for the delay.

I ran the code cleaner software on my VBAproject with success according to the software, yet still get the same error. Any other ideas?

Maybe this will help: I am not actually writing the code in 2007, but rather, I am writing in 2010 and sending to a computer with 2007 for compatibility testing. In 2010, the quit function works through use of the button most times, but occasionally has the same error. Also, sometimes in 2010 when I use the function, an error box pops up in the VBE window: "Out of Memory" it says. When this happens I notice that there is still an instance of my project open in the project explorer pane. I must close Excel completely and reopen for this instance to disappear.

Any ideas now? Any help would be greatly appreciated. I've tried so many things but this problem continues to linger!

Thanks!

[MsgBox]

Please Login or Register  to view this content.

This causes an error if the workbook hasn't been saved yet (and be given a path & name).

When you say the workbook needs to be given a path and name, do you mean that I must include it in the sub routine code? I have the workbook saved to my computer before the function is run. And remember also, the code executes perfectly when run from inside VBE, or manually from the "View Macros" list. Why does it screw up when I use a linked button?

Thanks for your input snb.

[MsgBox]

I have an update on this. I created a new workbook with a new VBAproject in excel and insert a module with the following code:

Sub QuitApp
ActiveWorkbook.Close SaveChanges:=True
End Sub

I then created a rectangle and assigned it the QuitApp macro. Clicking the rectangle resulted in successful execution of the workbook.close function the first time. Now each time I try, it produces the same error as above.

Am I using this function completely wrong?

[royUK]

There should be no backwards compatibility issues with that code.

On the new computer you should save the workbook to a directory then the code should be fine. The issue snb is referring to is one that would be encountered if the code is added to a new workbook that has not been saved prior to running the code

[MsgBox]

Ok, so I'm very confused. My steps are very basic:

Restart Computer
Open Excel
New Workbook
SaveAs Macro Enabled workbook -> Book1.xlsm
Open VBE
Insert Module
Type code:

Sub QuitApp
ActiveWorkbook.Close SaveChanges:=True
End Sub

Exit VBE
Insert rectangle on Sheet1
Right click "Rectangle 1" -> Assign Macro -> Select "QuitApp" -> OK
ctrl + S (Save)
Click "Rectangle 1"
Workbook closes and saves BUT triggers error (attached pic)
Excel application restarts completely

[royUK]

Can't tell from an image. Attach the problem workbook

[snb]

A wild guess; maybe it's got to do with the macro's name try:

Please Login or Register  to view this content.

[MsgBox]

I've tried now by naming the sub routine numerous different titles. I've attached the problem workbook. Note that this occurs for any workbook I attempt to run this fuction in. It crashes everytime the workbook.close is called from a button.

[snb]

Please Login or Register  to view this content.

[stefankemp]

Try this:

<Code>workbooks(ActiveWorkbook.name).Close SaveChanges:=True</Code>

By the way, how do you place code on this forum? I'm new here.

[stefankemp]

Oh, I found it (how to place code). So,

Try this:

Please Login or Register  to view this content.

[riff115]

Any resolution on this? I'm have precisely the same problem in Excel 2011

[arlu1201]

riff115,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.