Force "Save As" in Prompt Before Close - X Button

Hi everyone,

I need a macro that will replace the function SAVE in close prompt dialog ("Do you want to save the changes you made to book?"). It's possible to replace the function to SAVE AS, instead of SAVE (when a user modify a existing spreadsheet an then close it in X button, the file will be overwritten, and we want to change it). If isn't possible, can i disable the button SAVE from this prompt and keept the others (DON'T SAVE and CANCEL)?

And:

It's possible to open documents of certain paths as read-only? (example: if a documents is opened from My Documents and Desktop it will be read-only, but from others locations not).

Thank you in advance

Really thank you tehneXus. That was exactly what I needed.

But i found a issue. I created a XLAM addin, and added to THIS WORKBOOK (not to a module). When I close the spreadsheet (saving or not, the application remains opened (the XLAM addin - Want to save your change to Book1.XLAM to a File).
Then a I try to close it and the the save prompt is shown again.

If I choose YES, I get a error: Nr.:91 Object variable or With block variable not set.

If a choose NO, I got: N.:1004. Unable to get the Show property of the Dialog class.

I replaced ActiveWorkbook to ThisWorkbook, and the error was gone, but if I choose NO in the SAVE AS prompt, i get the SAVE prompt again (and then, can overwrite the file).

Originally Posted by tehneXus

HI,

I got it to work in an add-in but didn't found a solution to quit the application properly...

Add-In "ThisWorkbook" module

Please Login or Register  to view this content.

Add-In ClassModule called: "clsWbEvents"

Please Login or Register  to view this content.

Originally Posted by tehneXus

HI,

I got it to work in an add-in but didn't found a solution to quit the application properly...

Add-In "ThisWorkbook" module

Please Login or Register  to view this content.

Add-In ClassModule called: "clsWbEvents"

Please Login or Register  to view this content.

Thank you again tehneXus. It worked very well. When I close the spreadsheet, the application remain opened, but without the error that I mentioned before. In this case, I can manually close the application after closing the spreadsheet.
One last question: if I change the workbook references in the code to Document, is there possible to create a Word template, with this same code? (sorry for asking it in a Excel forum)

I'll try it.
Thank you.