Before save, prompt message box with yes/no/cancel. Yes calls Macro, No continues to save, Cancel exits sub

Question

I already have a successful "custom save" macro to save-as with a date stamp. I just want to have a message box ask to run it when someone tries to manually save. I essentially need "yes" to run the macro, "no" to save normally, and "cancel" to exit sub.

However, whenever I file>save, or ctrl+s, it just saves without prompting.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim answer As VbMsgBoxResult
    answer = MsgBox("Would you rather Save-As copy with date stamp?", vbYesNoCancel + vbQuestion + vbDefaultButton1, "You are overwriting the document!")

    If answer = vbYes Then
        Call filesave
    ElseIf answer = vbNo Then
        ActiveWorkbook.Save
    Else
        Exit Sub
    End If
End Sub

Answer 1

You need to set the Cancel from the sub procedure's arguments to True in order to halt the current save operation.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim answer As VbMsgBoxResult
    answer = msgbox("Would you rather Save-As copy with date stamp?", vbYesNoCancel + vbQuestion + vbDefaultButton1, "You are overwriting the document!")
    If answer = vbYes Then
        'Cancel the current standard save operation
        Cancel = True
        Call filesave
    ElseIf answer = vbNo Then
        'don't do anything; the standard save operation will proceed
    Else
        'Cancel the current standard save operation
        Cancel = True
    End If

End Sub