MsgBox with VBYesNoCancel

Hey!!

If suppose I write a code where at the time of closing the workbook a message box appears asking me if I wanted to make a back up of this file.

Clicking Yes would Make a back Up
Clicking No would Exit Workbook
And Clicking cancel would just cancel the Msg Box.

How should I write the code for the cancel button.

I have already written codes for the yes & no buttons.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As String
    Dim Ans As Variant
    Dim FileName As String
    Msg = ("Would you like to make a Back-Up of this file??")
    Ans = MsgBox(Msg, vbYesNo)
    If Ans = vbYes Then
        FileName = "C:\Users\J\Desktop\Back Up " & ThisWorkbook.Name
        ThisWorkbook.SaveCopyAs FileName
    End If
    If Ans = vbNo Then
        MsgBox "No Problemo!!"
    End If
End Sub

Selecting cancel prevents the workbook from closing

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As String
    Dim Ans As Long
    Dim FileName As String
    Msg = "Would you like to make a Back-Up of this file??"
    Ans = MsgBox(Msg, vbYesNoCancel)
    Select Case Ans
    Case vbYes
        FileName = "C:\Users\J\Desktop\Back Up " & ThisWorkbook.Name
        ThisWorkbook.SaveCopyAs FileName
    Case vbNo
        MsgBox "No Problemo!!"
    Case vbCancel
        Cancel = True
    End Select
End Sub

Perfect!!

Thank You.

Marking the thread as solved!