• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel crashes when file with Macro is closed

S

Sushil

Member
Hi,
Attached file crashes every time when it is not saved and closed if there is another file open in the same excel session. it does not crashes if it is the only file open in the session or if another file is open in a different excel session. Please help.

Thanks
Sushil
 

Attachments

  • Sept 5 Pentastar.xlsm
    122.5 KB · Views: 41
Hi Sushil ,

Can you check your file now ?

I made the following changes :

1. Moved the Application.EnableEvents = True to after the .Close statement , since otherwise it was calling the Workbook_BeforeClose event procedure again.

2. The .Close statement was as follows :

.Close savechanges:=False

I think this is not correct , since Excel normally capitalizes all keywords ; the savechanges keyword should have been changed to SaveChanges ; since it remained the same , I think Excel has not recognized it. I have changed the above statement to :

.Close (False)

Narayan
 

Attachments

  • Sept 5 Pentastar.xlsm
    121 KB · Views: 11
Hi Narayan
Thanks for your reply. the file still crashes . did you make the changes in the attached file. i did not see " Application.EnableEvents = True, .Close (False). in the code . Maybe i am looking at wrong place i am not too sure but the file still crashes
 
Hi Sushil ,

Then I am afraid I cannot help you ; I have tried it on my computer , and it does not crash Excel , where earlier it used to.

To double check , I downloaded the file I had uploaded , and tried it out , it closed without any problem.

To triple check , you can re-upload the file which still crashes , and let me check.

For your reference , I am posting the changed code :
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
   
    'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
        If Not .Saved Then
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
                vbYesNoCancel + vbExclamation)
                Case Is = vbYes
                    'Call customized save routine
                    Call CustomSave
                Case Is = vbNo
                    'Do not save
                Case Is = vbCancel
                    'Set up procedure to cancel close
                    Cancel = True
            End Select
        End If
   
    'If Cancel was clicked, turn events back on and cancel close,
    'otherwise close the workbook without saving further changes
        If Not Cancel Then
            .Saved = True
            .Close (False)
        End If
        Application.EnableEvents = True
    End With
End Sub
Narayan
 
@NARAYANK991
Hi!
4th and 5th checks: both the uploaded files from OP an you crashes after being hanged for a while if another workbook is open in the same Excel instance.
I tried to give a look at the code in the workbook class module but I found it not suitable for this time at night, half an hour to midnight: there are a lot of cancels, calls to custom save routines, worksheet events before close, before save, before the before, all mixed, all nested, that IMHO it'd be better to write it again from scratch than debug it, keeping the EnableEvents only in one place like before close event, don't use unnecessary cancels, and the list might go on.
Regards!
 
Hi Pablo ,

There may be several procedures in the module ; you need to look at only one viz. the Workbook_BeforeClose procedure.

The problem is specifically when the No option is selected , so there is no relevance of the other procedures.

Narayan
 
@NARAYANK991
Hi!
Thanks for the tip, I guessed so at a 1st glance, but when I tried to check this 6 cases: {file saved; file not saved} * {save yes; save no; save cancel}, I ended walking thru all the others like Custom_Save, BeforeSave event, and in all them there are cancel settings and enabling / disabling events. So I passed, waiting for my NCIS Friday night episode.
Regards!
 
Hi Narayan,
it still crashes and also now it doesnt hide all the sheets except" Macros" sheet when the file is opened.

Thanks
Sushil
 
I was getting some mysterious crashes on a file, so I installed and ran the free CodeCleaner utility:
http://www.appspro.com/Utilities/CodeCleaner.htm

Here's the blurb on it:
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.

This fixed the issue for me. Suggest you install this, and give it a try.
 
Hi Sushil,

Why do you have to force close the workbook with the line of code? (.Close savechanges:=False changed to .Close (False) by NARAYANK991) .

The Workbook_BeforeClose event expects the workbook to be alive until it executes completely and closes the file on its own. I guess, since you are closing the workbook within the event, this terminates the workbook and inturn all the events running on it. This could be a case why it is crashing.

Also, if you notice Excel crashes only after a pause when the file got closed. I commented out that line, executed and all worked fine.
 
I tried this code on Book1.xlsm and managed to crash it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Close vbNo
End Sub
 
Hi Lohith,
in regards to macros , i am in junior kindergarten. i just got few macros from internet doing google and combined them to suit my needs. iam not trying to force close the workbook with the line of code .Close savechanges:=False . it was in the macro which i copied. All i am trying is to force the user to enable macros when they open the workbook and when they make changes and close the workbook it automatically refreshes the pivot table and save and close the workbook. If you can change the code and eliminate the problem , i will highly appreciate it. it is causing a lot of headache to me and lot of the users.

Thanks
Sushil
 
Hi Sushil ,

Surely you must have read Lohith's post on what the problem was ?

All you have to do is remove the line :

.Close savechanges:=False

from your original file ; since your original file was OK in all other respects , removing this one line will remove the one problem that you were facing. Or are there other problems ?

Narayan
 
Hi Narayan,
i tried taking out the line Close savechanges:=False. it does not crash but then next time i open the file , MACROS tab does not open up automatically and ask user to enable the macros. this file is to be used reapeatedly every week and has to show same message "Please enable the macros" every time.


Thanks
Sushil
 
Replace your Workbook_BeforeClose event with this:

Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
    
    'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook
        Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _
            vbYesNoCancel + vbExclamation)
            Case Is = vbYes
                Call CustomSave
                ThisWorkbook.Saved = True
            Case Is = vbNo
                ThisWorkbook.Saved = True
            Case Is = vbCancel
                Exit Sub
        End Select
    
    End With
    
    Cancel = False
End Sub
 
Hi Sushil ,

The problem has to do with the file being opened without the configuration being put in place. If the Welcome screen needs to be displayed every time the file is opened , this should be taken care of in the Workbook_Open event procedure.

Your existing Workbook_Open procedure has a call to ShowAllSheets ; I have changed it to HideAllSheets. Check it now.

Narayan
 

Attachments

  • Sept 5 Pentastar.xlsm
    122 KB · Views: 15
Narayan - When I open this file from the link above, clicking on Enable Macros is not unhiding the other sheets and hiding the Macros sheet. So possibly more tweaking required.

Also note that my code above seems to work fine...it comes from one of my own projects. I had a similar issue to Sushil some time back, and spent ages trying to track it down.
 
Hi Jeff ,

That is for Sushil to attempt !

I do not know under what circumstances he would like all sheets to be displayed ; the message merely requests the user to enable macros , and according to Sushil's post , he was not getting this Welcome screen when the file is opened. Hopefully this problem is resolved !

Narayan
 
Ahh Narayan...I see what's tripped you up. The 'Welcome' screen is meant to be displayed BEFORE macros are enabled, because it warns users that they need to enable macros in the event that they haven't already.

So whenever the file is saved, it needs to be saved with the 'Welcome' screen the only one visible.

And as soon as the user enables macros, that 'Welcome' screen should be hidden by the code, and the other tabs unhidden.
 
Hi Jeff ,

OK. In that case , the Workbook_Open procedure should call ShowAllSheets , and the CustomSave procedure should call HideAllSheets.

Let us see what Sushil posts ; if he can make these changes it's fine , else we can upload a revised workbook with this change incorporated , unless you have already done that.

Thanks.

Narayan
 
You must log in or register to reply here.
Back
Top