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

Option Explicit

Private MySheetHandler As clsWbEvents

Private Sub Workbook_Open()
    Set MySheetHandler = New clsWbEvents
End Sub

Add-In ClassModule called: "clsWbEvents"

Option Explicit

Public WithEvents xlApp As Application

Private Sub Class_Initialize()
    Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    
    Dim vSaveAs
    On Error GoTo ErrHandler
    
    If Wb.IsAddin Then Exit Sub
    
    xlApp.EnableEvents = False
    xlApp.DisplayAlerts = False
    
    Select Case VBA.MsgBox("Want to save your change to '" & Wb.Name & "' to a file?", vbYesNoCancel + vbExclamation, "Microsoft Excel")
        Case vbCancel
            Cancel = True
            GoTo ErrHandler
        Case vbNo
            Cancel = True
            Wb.Close False
            GoTo AppQuit
        Case vbYes
            vSaveAs = xlApp.Dialogs(xlDialogSaveAs).Show(arg1:=Wb.Name)
            Select Case vSaveAs
                Case False
                    Cancel = True
                Case Else
                    Wb.SaveAs Filename:=vSaveAs
                    Wb.Close False
                    GoTo AppQuit
            End Select
    End Select
    Err.Clear
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Nr.: " & Err.Number & vbLf & Err.Description
    End If
    Err.Clear
    xlApp.DisplayAlerts = True
    xlApp.EnableEvents = True
    Exit Sub
AppQuit:
    Set vSaveAs = Nothing
    Set Wb = Nothing
    If xlApp.Workbooks.Count = 0 Then xlApp.Quit
    Set xlApp = Nothing
End Sub

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

Option Explicit

Private MySheetHandler As clsWbEvents

Private Sub Workbook_Open()
    Set MySheetHandler = New clsWbEvents
End Sub

Add-In ClassModule called: "clsWbEvents"

Option Explicit

Public WithEvents xlApp As Application

Private Sub Class_Initialize()
    Set xlApp = Application
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    
    Dim vSaveAs
    On Error GoTo ErrHandler
    
    If Wb.IsAddin Then Exit Sub
    
    xlApp.EnableEvents = False
    xlApp.DisplayAlerts = False
    
    Select Case VBA.MsgBox("Want to save your change to '" & Wb.Name & "' to a file?", vbYesNoCancel + vbExclamation, "Microsoft Excel")
        Case vbCancel
            Cancel = True
            GoTo ErrHandler
        Case vbNo
            Cancel = True
            Wb.Close False
            GoTo AppQuit
        Case vbYes
            vSaveAs = xlApp.Dialogs(xlDialogSaveAs).Show(arg1:=Wb.Name)
            Select Case vSaveAs
                Case False
                    Cancel = True
                Case Else
                    Wb.SaveAs Filename:=vSaveAs
                    Wb.Close False
                    GoTo AppQuit
            End Select
    End Select
    Err.Clear
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "Nr.: " & Err.Number & vbLf & Err.Description
    End If
    Err.Clear
    xlApp.DisplayAlerts = True
    xlApp.EnableEvents = True
    Exit Sub
AppQuit:
    Set vSaveAs = Nothing
    Set Wb = Nothing
    If xlApp.Workbooks.Count = 0 Then xlApp.Quit
    Set xlApp = Nothing
End Sub

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.