ThisWorkbook.Save or Me.Password = "123" maybe causing excel file crash every time on Save and Close command!!

[PritishS]

Dear Sir/Madam,
Good Day!!

I'm stuck with a strange problem and can not get any solutions.

Problem: I have a macro enabled workbook say ABC.xlsm. I have code in ThisWorkbook for saving the file while closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

and Workbook_BeforeSave event have a code to reset password of workbook, if any one try to change it.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Me.Password = "123"
End Sub

This file is crashing in two cases--

Case 1: When I close excel file by clicking red 'X' close button.
Case 2: Wherever I used ThisWorkbook.Save code in any of my code module.

What I have tried yet:
1. I debug through any code which is causing crash using F8. I found whenever code reached to ThisWorkbook.Save, it crashes.
2. Also I have used 'DoEvents' after ThisWorkbook.Save in Workbook_BeforeClose event. But didn't work.

3. Then I had a doubt that maybe before going in ThisWorkbook.Save, code is trying to reset the password. So I commented that password line and it never crashed since.
Removed comment from Me.Password = "123" and tried 'save' and excel got crashed!!

My question:

Please guide me why this is happening??
Moreover I have other excel macro enabled files where I use this Workbook_BeforeSave and Workbook_BeforeClose combination. Each of those files are working smooth since last 2-3 years.

Kindly suggest if anyone having the same experience and solution.

Thanks & Regards,
PritishS

 

[Coding4Fun]

The code you posted works fine, I would suspect the issue resides elsewhere in the code or file set up. When you say it "crashes" what happens? Does it just close, or are you getting an error?

Try changing your code to this and see what it returns

Private Sub Workbook_BeforeClose(Cancel As Boolean)


On Error GoTo myError1:
ThisWorkbook.Save


Exit Sub


myError1:
    MsgBox (Err.Number & "-" & Err.Description)


End Sub


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




On Error GoTo myError2:
Me.Password = "123"


Exit Sub


myError2:
    MsgBox (Err.Number & "-" & Err.Description)


End Sub

 

[PritishS]

Hi!

Sorry for delay reply!!

When you say it "crashes" what happens? Does it just close, or are you getting an error?

It goes into "Microsoft Excel has stopped working"- Check online for a solution and restart the program---Restart the program.

I tried to catch the error as you mentioned..but unfortunately during debug (F8), whenever it reaches to 'ThisWorkbook.Save' code, excel crashing!
Any suggestions or work around please!!

Thanks & Regards,

PritishS

 

[Coding4Fun]

I am thinking it might be related to something else in the workbook.

If you create a brand new workbook and put just the code to save the workbook do you still get the error with this new workbook?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub