Help with Workbook_BeforeSave

[Ghost662]

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!

[SamT]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     
Dim Name As String
Dim Answer As Variant
   
  Answer = MsgBox("Unlink and Save (Yes), Save As Is (No), Don't Save (Cancel)", vbYesNoCancel)
  
  If Answer = vbNo Then
    'Let the Workbook do the work
    Exit Sub
  ElseIf Answer = vbCancel Then
    'Don't Save
    Cancel = True
    Exit Sub
  Else
    'This sub will do the saving
    Cancel = True
    Name = Application.GetSaveAsFilename
    
    'If the user Cancels at this time
    If Name = False Then Exit Sub
    
    Unlink_Data
    Me.SaveAs Name
  End If
End Sub

[Ghost662]

Thank you for responding SamT! Your code didn't quite solve the problem, but it helped me figure out a solution!

It took me a bit to understand why you put Cancel = True in the Else case (in fact, I kept modifying it thinking it might be a mistake after the code didn't work). But then I realized (after setting it to False) that's because we're otherwise calling the Save As prompt twice and we want to run a macro first and take care of the save ourselves. However, then the MsgBox started popping up twice for Yes and No (whereas before it was just the No case).

That's when I realized we were in a loop. The user tries to save the workbook, selects Yes or No, and the code tries to save, which triggers the BeforeSave event again... and then excel crashes.

Here's the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    
    Dim Name As String
    Dim Answer As Variant
    
    Answer = MsgBox("Unlink and Save (Yes), Save As Is (No), Don't Save (Cancel)", vbYesNoCancel)
    
    If Answer = vbNo Then
        Exit Sub
    ElseIf Answer = vbCancel Then
        Cancel = True
        Exit Sub
    Else
        Cancel = True
        Name = Application.GetSaveAsFilename
        
        Application.ScreenUpdating = False
        Unlink_Data
        Application.ScreenUpdating = True
        
        If Name = "False" Then Exit Sub
        
        Application.EnableEvents = False
        Me.SaveAs Name
        Application.EnableEvents = True
        Exit Sub
    End If


End Sub

Thanks again!

[Ghost662]

Actually, I think your code might have worked, but between my post and your response, I realized that I forgot to change the second set of EnableEvents and ScreenUpdating to True (I was copying and pasting). My fault!

[SamT]