Excel crashes when reopening file after 'ActiveWorkbook.Close'

[oCoCarbon]

I have an odd one here. It took me a while to track down where the problem is but now it seems to be isolated to asking the user if they're sure before calling ActiveWorkbook.Close.

The macros below runs when you click an icon on the worksheet.

Sub CloseFile()

Dim answer As Variant
    Application.ScreenUpdating = False
    answer = MsgBox("Are you sure you want to exit?", vbYesNo, "Title") 'idiot test
    If answer = vbYes Then
        ActiveWorkbook.Close
    End If
    Application.ScreenUpdating = True
End Sub

The code executes just fine - the message box comes up, if you cancel it there's no problems, and if you don't cancel it the file closes. But then when you reopen the file it comes up with the error message "Microsoft Excel has stopped working, etc" and then goes through a restart.

The error seems to be because of the "idiot test". If you comment it out there's no problem. Same if you close with Ctrl-W. I can live without the test but it would be good to know why it's creating the error.

I'm running Excel 2010 by the way.

 

[Norie]

If the code is meant to close the workbook it's in try using ThisWorkbook instead of ActiveWorkbook.

ThisWorkbook.Close

 

[oCoCarbon]

No joy. That still produces the same results I'm afraid.

I've tried to reproduce the effect in another workbook and can't get it to crash in the same way. However if I close the test workbook while the problem workbook is still open I get the same crash but on closing rather than reopening. It seems like making the problem worksheet visible is causing the crash.

 

[Norie]

You aren't making any worksheet visible in that code.

 

[oCoCarbon]

Sorry, that's "make visible" as in by closing the workbook that was hiding it, not set it's visible property!

Actually it's gone really intermittent now. Removing the idiot test hasn't solved the problem but now it occurs only every few times of closing and reopening. And it's doesn't seem to affect the test sheet at all any more. I'm thinking I'll either try rebuilding the whole thing up from the test one, or stripping back the problem one until it starts behaving.

 

[oCoCarbon]

I think I've managed to sort it now. The problem seems to have been related to firing the macro from a label (which I was using to sit over the icon image and the text in a cell beside it to make it act like one big button). I tried it with a label on the test workbook and the thing started crashing just like the real workbook.

So I then tried firing it from a regular form button and the problem went away. Also worked for the real workbook, and there also doesn't seem to be a problem with assigning the macro directly to the icon. To keep the desired part of using the label - acting like a bigger button - I used the crop image tool to extend the borders of the image beyond its actual size to cover the text alongside to it.

That was seriously more trouble than it was worth. I hate erratic bugs like that! Thanks for your input, Norie. I probably would have given up if I hadn't wanted to report back with a resolution!

 

[Norie]

A label over an icon/image? Why?

That just sounds like a bad idea.

Couldn't you just have assigned a macro to the icon/image?

 

[oCoCarbon]

The reason was that I was having trouble assigning a macro to an icon/image in a chartsheet. The only workaround I could come up with was to put a label over the icon. And then I just used the same trick in the worksheet - when obviously now I think about it a transparent shape would be a much better option. Didn't think it would cause this much trouble though!