Get a prompt to file SaveAs before closing the workbook

[Mieke01]

Hello

Does anyone know vba code to bring up a file Save As dialogue box when the user comes out of the file? I have been looking around for the code, but no matter what I put, nothing is happening and the file just closes when I click the "x" at the right.

Thank you

Mieke

 

[GTO]

...but no matter what I put, nothing is happening and the file just closes when I click the "x" at the right.

Are you saying that after making changes, that when you go to close the file the 'Do you want to save?' dialog fails to appear? OR, that if there are unsaved changes, you'd rather have the SaveAs dialog pop-up?

 

[Mieke01]

Hi,

Yes, I would like to see the Save As box popping up whenever the user has made changes and the file is closed. Also, as I am working with a template I want to prevent them to just save over the existing file.
Thank you for your help!

Mieke

 

[GTO]

Hi,

Yes, I would like to see the Save As box popping up whenever the user has made changes and the file is closed. Also, as I am working with a template I want to prevent them to just save over the existing file.
Thank you for your help!

Mieke

Okay, I am logging out, but I should be able to check back tonight. In the meantime, for me or any 'answerer',

• When you say 'working with a template', does this mean with an actual Excel template (.xlt, .xltx or .xltm format) ? If so...
  • Is the user creating a new workbook 'manually' by double-clicking the template file?
  • Or - is the new workbook being created programmatically?
• Or - do you mean a 'master' workbook that you are hoping the user SaveAs rather than overwriting through Save?

Mark

 

[Mieke01]

Hi Mark,

I mean a "master" workbook that I created and that I would like to protect.

Thank you for your help.

Mieke

 

[GTO]

Hi Mieke,

I would suggest that the easiest and at least effective for any user not bent on overwriting the original workbook is to simply change the read-only property of the workbook (with the workbook closed) in Windows Explorer. In Windows Explorer, right-click the file and select Properties from the context menu. Tick Read-only, click Apply and OK.

To overwrite, the user would need to un-set the read-only property. The advantage of this method is that no unnecessary code is saved with the SaveAs.

Does that help?

Mark

 

[Mieke01]

Hi Mark,

I am not sure this would do what I am after. What I am trying to create is a spreadsheet in a certain format where the sales people can log their expenses. However, they seem to have trouble with saving their spreadsheet before they close it or indeed save it before they clear it for the next batch of expenses they want to log. Therefor, I would like to have a prompt that comes up to save the spreadsheet under Save As when they exit the workbook. Is that possible?

Thanks

Mieke

 

[GTO]

...However, they seem to have trouble with saving their spreadsheet before they close it or indeed save it before they clear it for the next batch of expenses they want to log. Therefor, I would like to have a prompt that comes up to save the spreadsheet under Save As when they exit the workbook. Is that possible?

If you mark the file as Read-only, the Save As dialog will pop-up. I am suggesting you try this first, or, in the alternative, simply save your 'master' workbook as a legitimate template file (.xlt/.xltm) as these methods will force a Save As without coding that forces Save As on the secondary/created workbook (as the code to force a Save As will be in the new/secondary workbook).

Mark

 

[Mieke01]

Hi Mark,

Thank you for this tip. I have done that and it does the job fine. I have also a "clear data" button in my spreadsheet and would like to have the same warning. So when it gets clicked, a message appears to ask the user if they haves saved the document and if they say "no" bring the SaveAs dialogue up with .xlsx in the file name.
This is what I got so far, but it does not bring .xlsx up. It displays *.* only. Would you know the code that I need to add?

If MsgBox("You are about to erase the information in your expense form. Have you saved the form?", vbYesNo) = vbNo Then Application.GetSaveAsFilename

Mieke

 

[GTO]

Hi Mark,

Thank you for this tip. I have done that and it does the job fine...

Hi Mieke,

When you say "I have done that...", do you mean you saved/changed the 'master' workbook as an actual template (.xlt/.xltm) or do you mean you marked the 'master' file as read-only? It probably will not influence our code., but I like to have as clear a picture as possible.

Thank you,

Mark