CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 503

How do you close a workbook during BeforeSave event if condition not met?

I am trying to close a workbook during the BeforeSave event if a condition is not met. I am using the following code and the save is canceled as expected but the close event crashes excel.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If X <> Y then
        Cancel = True
        ThisWorkbook.Close savechanges:=False
    End If
End Sub

I have even tried to call another Sub with the close command in it but that also produces the same issue

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If X <> Y then
        Cancel = True
        ExitNoSave
    End If
End Sub

Public ExitNoSave ()
    ThisWorkbook.Close savechanges:=False
End Sub

Here is a screen grab from the code I made a new workbook with only the before save event and it still happens.

enter image description here

enter image description here

Does anyone have a way to do this or is it not possible to do this from the BeforeSave event?

Upvotes: 1

Views: 773

Answers (1)

Matt
Matt

Reputation: 503

Ok guys I figured it out you have to use the AfterSave event to accomplish this now apparently so if you cancel the save with the BeforeSave event it passes on that save failed to the AfterSave event you can then write your code for different conditions.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If 1 <> 2 Then
        Cancel = True
    End If
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success = False Then
        ThisWorkbook.Close savechanges:=False
    End If
End Sub

Upvotes: 1

Related Questions