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:

The Yes option works, but crashes excel (but the macro runs and the user inputted name and path are saved).
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]

:clap: