"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

Take Care