MsgBox comes up twice using If answer = Yes/No/Cancel

Question

I am trying to create a macro that will give me a message box before closing Excel to ask if I want to save changes. When I select No from the message box the message pops up again, then the 2nd time that I chose No it does close.

I don't know very much about VBA programming, I just try to record macro's then edit them slightly. I also search online and copy paste. This is how I put together this code below but obviously something is wrong. (although it works) I wonder if someone could advise me what needs to be changed in this code. Any help appreciated, Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim answer As String
    Dim question As String
    Dim OrigName As String


    question = "Do you want to save Changes?"
    answer = MsgBox(question, vbYesNoCancel)

    If answer = vbCancel Then
        Exit Sub
    End If

    If answer = vbNo Then
        ActiveWorkbook.Close SaveChanges:=False
    End If

    If answer = vbYes Then
        ActiveWorkbook.Save
        ActiveWorkbook.SaveAs ("C:\Users\me\Documents\reports\Backup\" + ActiveWorkbook.Name & Format(Now(), "DD-MMM-YYYY hh-mm") & ".xlsm")
        Exit Sub
    End If

End Sub

score 2 · Accepted Answer · answered Nov 08 '18 at 23:37

Try this:

You're already closing the workbook, so there's no need to Close it again in your code. Just 'trick' Excel to think changes are saved with ThisWorkbook.Saved = True (Note: This only tells Excel that the changes were saved - it doesn't actually save them) and it won't prompt you to save changes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Select Case MsgBox("Do you want to save Changes?", vbYesNoCancel)
    Case vbCancel
        Cancel = True
    Case vbNo
        ThisWorkbook.Saved = True
    Case vbYes
        ThisWorkbook.Save
        ThisWorkbook.SaveAs ("C:\Users\me\Documents\reports\Backup\" + ActiveWorkbook.Name & Format(Now(), "DD-MMM-YYYY hh-mm") & ".xlsm")
    End Select

End Sub

And using ThisWorkbook in your case would be a better object rather than using ActiveWorkbook.

MsgBox comes up twice using If answer = Yes/No/Cancel

1 Answers1