"Save As" prompt

[K. Georgiadis]

Hi folks,

is it possible for Excel to display a message box when the user tries to close the file, the message being: "do you want to save this workbook under a different name?" with the choices of "Yes" or 'No." If "No" Excel proceeds to close the file, if "yes" the process is aborted so that the user can save the file under a different name.

Thanks for your help.

KG

[Jacob Hilderbrand]

Try this. Put the code in the ThisWorkbook code section.



Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Prompt As String
Dim Title As String
Dim ShowSaveAs As VbMsgBoxResult
Prompt = "Do you want to save this file with a new name?"
Title = "Save As Prompt"
ShowSaveAs = MsgBox(Prompt, vbYesNo, Title)
If ShowSaveAs = vbYes Then
Application.Dialogs(xlDialogSaveAs).Show
End If
End Sub

[K. Georgiadis]

it works great. Thanks.
I'll mark as "SOLVED"

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care