Solved: Self message to save file before closing it

[brunces]

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.

[vba]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
[/vba]

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

[lucas]

Why even have a messagebox.....just use the beforeclose event to hide the sheets whether it was saved before or not.

[lucas]

put this in workbook open event so that the worksheet is always opened to that sheet:
[VBA]
Sheets("Plan1").Activate
[/VBA]

[lucas]

This should solve your problem if you wish to keep the messagbox:
[VBA]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
Application.DisplayAlerts = False
Application.Quit
Else:
Sheets("Plan1").Select
Range("A1").Select
Sheets("Sheet2").Visible = False
Sheets("Plan3").Visible = False
ThisWorkbook.Save
Application.Quit
End If
End Sub[/VBA]

[brunces]

Why even have a messagebox.....just use the beforeclose event to hide the sheets whether it was saved before or not.

Please, how? Maybe it's a better solution.

Your code with message box works perfectly well. Thanks a lot.

Brunces

[lucas]

You can try this on workbook open and you may be able to get around worrying about what happens when they close the file...just a suggestion.
[VBA]Private Sub Workbook_Open()
Sheets("Plan1").Select
Range("A1").Select
Sheets("Plan2").Visible = False
Sheets("Plan3").Visible = False
End Sub
[/VBA]

[brunces]

I see... Then, there's no need of BeforeClose codes, right?

Cool! It works fine too. Better! Thanks a lot, Lucas.

[lucas]

You're very welcome....

[brunces]

Oops! Lucas, I just found a problem... If Excel is set not to run macros, when someone opens the file, those sheets won't be hidden.

For example, I save the file here, at my computer, and send it to someone whose Excel is set not to run macros. Then, when this person opens the file, he will see those sheets which were supposed to be hidden.

I think it's better to hide them before closing file, isn't it?

Another thing... That code of yours tells Excel to quit, but I want it to just close the file. Is it possible?

Thanks.

[lucas]

I think it's better to hide them before closing file, isn't it?

You have to sort through the problems that might arrise and figure out the logic...sounds like that might be best for your situation.

[brunces]

Yeah! What about the code? Is it possible to just close the file, instead of quiting Excel?

[lucas]

Try it like this. Put this in the thisworkbook module:
[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim Prompt As String
Dim Title As String
Dim MyResponse As VbMsgBoxResult
Prompt = "Have you updated the Spreadsheet"
Title = "Insert Title Here"
MyResponse = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
If MyResponse = vbYes Then
Call Macro1
End If

End Sub[/VBA]
and put this in a standard module:
[VBA]Sub Macro1()
ActiveWorkbook.Save

End Sub[/VBA]
example attached

[brunces]

Man, you're coooool! Thank you very, very much!

[lucas]

The reason I think you are getting the message twice is because you have a workbook close event inside of a workbook close event.....