• 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 i close the file having macro.

S

Sushil

Member
Hi,

I have a following macro in a worksbook. whenever i close this workbook and another work book is open.. excel crashes. Only time excel does not crash is if i open another excel session and if there i open macro enabled work book. Please let me know why this is happening. Thanks in advance for your help.


Thanks

Sushil


Sub Update()

With ActiveSheet

.Protect Password:="PASSWORD", UserInterfaceOnly:=True

For Each pt In .PivotTables

pt.RefreshTable

Next pt

End With

End Sub


Sub Insert_Row()

ActiveSheet.Unprotect Password:="PASSWORD"

If Selection.Rows.Count > 1 Then Exit Sub

With Selection

.EntireRow.Copy

.Offset(1).EntireRow.Insert

Application.CutCopyMode = False

On Error Resume Next

.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

On Error GoTo 0

ActiveSheet.Protect Password:="PASSWORD"

End With


End Sub


Sub delete_row()

ActiveSheet.Unprotect Password:="PASSWORD"

ActiveCell.EntireRow.Delete

ActiveSheet.Protect Password:="PASSWORD"

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


End Sub
 
Hi, Sushil!

This three procedures by themselves shouldn't interfere with the process of closing a workbook. Unless that workbook has VBA code in the workbook before close, before save or after save events. It'd be useful if you consider uploading the file.

Regards!
 
This is the first time i used drop box to upload the file. not sure whether it will work.


https://www.dropbox.com/s/qwwyseu4n464bhp/Jan%2010%20Center%20Support.xlsm?m


if you open this file along with your other excel files open and then you close it without saving ..excel crashes.


Thanks

Sushil
 
Good day Sushil


Sorry but can not help, downloaded your file and no matter what I do I can not get it to crash
 
Hi, Sushil!

I downloaded it, opened, modified, closed, ... and Excel crashed. Will give it a look tomorrow.

Regards!
 
that is really strange .. it crashes not only on my computer but also on any other computer at my work place. the only time it does not crash is if no other excel file is open / or other excel files are open in different excel session/ or if the file is saved and then closed.. if not saved then it crashes


Thanks

Sushil
 
The problem is happening in the Workbook_BeforeSave event. I will excerpt the code here in case someone sees a problem immediately:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'to run a macro named update


Call Sheet1.Update

'Turn off events to prevent unwanted loops

Application.EnableEvents = False


'Call customized save routine and set workbook's saved property to true

'(To cancel regular saving)

Call CustomSave(SaveAsUI)

Cancel = True


'Turn events back on an set saved property to true


ThisWorkbook.Saved = True

Application.EnableEvents = True

End Sub
 
Sushil


I tried to close with save, without save, open on its own, opened with other files open, no crash. You say it crashes on another computer at your work place could there be an issue with network/server or the file extension.
 
Hi, Sushil!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Excel%20crashes%20when%20i%20close%20the%20file%20having%20macro.%20-%20Jan%2010%20Center%20Support%20%28for%20Sushil%20at%20chandoo.org%29.xlsm


I added a boolean variable to avoid reentering in the close event when not wanted to save, bAction. The code of the before close event is modified as this:

-----

[pre]
Code: 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Stop
Dim bAction As Boolean
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
bAction = 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
bAction = True
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 = True And Not bAction Then
.Saved = True
Application.EnableEvents = True
.Close savechanges:=False
Else
Application.EnableEvents = True
End If
End With
End Sub
[/pre]
-----


After testing and if it works ok, remove the Stop statement, it's for debugging purposes only. Just advise if any issue.


Regards!
 
Thanks! works like a charm. only minor issue is that it asks twice whether i want to save or not, when i take out stop. Thanks again.


Sushil
 
Hi, Sushil!

Glad you solved it. And about the double ask, I think it was previously set like that, precisely I avoided the second trigger of the before close event and changed nothing else, I hope. Do you think you can handle to manage the modifications? Maybe with an Application.DisplayAlerts set to false and then to true it'd be enough. If not, just advise.

Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Sir,

I am like in Junior Kindergarten in case of macros. i just took individual macros doing google and just combined them together to suit my needs and i have no idea how to modify it to avoid the double ask of whether to save or not. I will highly appreciate if you can help in that too..


Thanks

Sushil
 
Hi, Sushil!


This will be expensive, very expensive, outrageously expensive, ...


It's Friday and you're making me read carefully a VBA code that I assume isn't yours so I can't ask you anything about it...


Well, let me explain this:

a) when you attempt to close a workbook the before close event code (if exists) is executed

b) when you manually or automatically save a workbook the before save event code (if exists) is executed

c) in your case the before close event when you say you want to save your workbook calls a procedure called CustomSave

d) in your case the before save event calls the procedure Sheet1.Update (that protects active sheet) and then calls the procedure CustomSave

e) so the code in the before close event isn't necessary at all, and considering that is the code that isn't working fine, try renaming the procedure to Workbook_BeforeClose1 and test all possible options

f) if it worked fine, let the procedure renamed or remove it entirely


Then tell us about the results.


Regards!
 
Hi, Sushil!


If I could put my hands on the guy who built this code...


There's an additional modification you should do to avoid recursive saving prompt. Change before save event code to this:

-----

[pre]
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'to run a macro named update

Call Sheet1.Update
'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
Call CustomSave(SaveAsUI)
'    Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
'    ThisWorkbook.Saved = True
End Sub
[/pre]
----


I commented the "Cancel = True" statement as it shouldn't be cancelled if the save has been done, and the "ThisWorkbook.Saved = True" statement as after saving the workbook another procedure that modifies it is called (ShowAllSheets).


Test it with this changes.


Regards!
 
i made the two changes mentioned above in the before save code , but unfortunatley it does not correct the double ask.


Thanks

Sushil
 
Hi, Sushil!


Here's the same file updated with the changes I mentioned:

https://dl.dropbox.com/u/60558749/Excel%20crashes%20when%20i%20close%20the%20file%20having%20macro.%20-%20Jan%2010%20Center%20Support%20%28for%20Sushil%20at%20chandoo.org%29.xlsm


I opened the file, changed any value, clicked on "X" to close, got displayed the prompt, said "No" and didn't appeared again. Repeated the steps, said "Yes" and didn't appear again.


Would you please check it?


Regards!
 
yes .. your are right! maybe i did something wrong... thank you very much for your help. i really appreciate that.


Thanks

Sushil
 
Hi, Sushil!

You're welcome... and take care next time you put together pieces of VBA code. As a guideline, if they're events code, put a stop at the start of each one, and then you can trace which, when and how many times they're executed.

Regards!
 
Hi ,

for some reason now when the file is opened, it does not force the user to enable macro and the pivot table does not gets refreshed automatically when file is saved. Thanks for the help in advance.


Thanks

Sushil
 
Hi, Sushil!


The macro behaviour at open time is not related to the containing workbook itself but the Excel configuration options & settings regarding macros and VBA code.


If I don't remember and lightly just checked looking at the ThisWorkbook code, it'll all depends on which sheets are visible at save time.


The involved code is this:

-----

[pre]
Code: 
Option Explicit

Const WelcomePage = "Macros"

Private Sub Workbook_Open()
'Unhide all worksheets
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
End Sub

Private Sub ShowAllSheets()
'Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub
[/pre]
-----


I can't analyze it further now, but you'd get sure of:

- macros are all enabled or disabled with notification (from Excel options)

- open macro is executed (set a breakpoint with F9 at the first line of that code)

- operation as described in worksheet Instructions is Ok


Hope it helps.


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