I'm trying to implement a BeforeSave event where they select Yes, No, or Cancel in which:
  • Yes: Runs a macro (Unlink_Data) then shows the Save As prompt and saves the workbook as whatever they type in.
  • No: Doesn't run the Marco and just Saves the current name and file path (just like Ctrl + S)
  • Cancel: Cancels the save


I'm having two issues with the code below:
  1. The Yes option works, but crashes excel (but the macro runs and the user inputted name and path are saved).
  2. The No option also works, but displays the initial MsgBox twice and does whatever the second selection is (so if you select No and then Yes, it'll run the macro).


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

Dim Name As String


    a = MsgBox("Unlink and Save (Yes), Save As Is (No), Don't Save (Cancel)", vbYesNoCancel)
    If a = vbYes Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Unlink_Data
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        Name = Application.GetSaveAsFilename
        
        If Name <> "False" Then
            ActiveWorkbook.SaveAs Name
        Else: Cancel = True
        End If
        
    ElseIf a = vbNo Then
        ActiveWorkbook.Save
    Else
        a = vbCancel
    End If


End Sub
Thanks in advance for any help you can provide!