brunces
06-27-2007, 07:16 AM
Friends,
Please, I've been trying to create a code which would replace that little message box which appears when we close a file without saving it, I mean, Excel asks us to save the document or not (or cancel).
The point is: I need to force the user to save or not the document before closing file because if he saves it, Excel should hide some sheets, and if he doesn't save it, there's no need of hiding them, so Excel should simply close the file. That's it. So, even if the file has already been saved, when the user closes it, this message box should appear.
I've created this code, but I'm not sure if it's correct or if it's the right way to do it.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("You've changed data in this file. Would you like to save it?", vbYesNo, "Alert") = vbNo Then
ThisWorkbook.Close SaveChanges:=False
Exit Sub
Else:
Sheets("Plan1").Select
Range("A1").Select
Sheets("Plan2").Visible = False
Sheets("Plan3").Visible = False
ThisWorkbook.Close SaveChanges:=True
End If
End Sub
As I said, I don't know if it's the right code. It's working! It really replaced the default message box, but there's a little problem: this message appears TWICE before closing. Why?
Please, is the code correct? Do I have to change anything? Is there any other smoother code I could use? Please, help me! :)
Thank you very much for your attention.
Hugs, :)
Brunces
Please, I've been trying to create a code which would replace that little message box which appears when we close a file without saving it, I mean, Excel asks us to save the document or not (or cancel).
The point is: I need to force the user to save or not the document before closing file because if he saves it, Excel should hide some sheets, and if he doesn't save it, there's no need of hiding them, so Excel should simply close the file. That's it. So, even if the file has already been saved, when the user closes it, this message box should appear.
I've created this code, but I'm not sure if it's correct or if it's the right way to do it.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("You've changed data in this file. Would you like to save it?", vbYesNo, "Alert") = vbNo Then
ThisWorkbook.Close SaveChanges:=False
Exit Sub
Else:
Sheets("Plan1").Select
Range("A1").Select
Sheets("Plan2").Visible = False
Sheets("Plan3").Visible = False
ThisWorkbook.Close SaveChanges:=True
End If
End Sub
As I said, I don't know if it's the right code. It's working! It really replaced the default message box, but there's a little problem: this message appears TWICE before closing. Why?
Please, is the code correct? Do I have to change anything? Is there any other smoother code I could use? Please, help me! :)
Thank you very much for your attention.
Hugs, :)
Brunces