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

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
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.

Code:
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.

Code:
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??:confused:
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
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
360
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

Code:
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
 
Last edited:

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
113
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!!:confused:

Thanks & Regards,

PritishS
 
Last edited:

Coding4Fun

Active Member
Joined
Feb 16, 2017
Messages
360
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?

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

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top