Message Box Pop up twice while closing workbook event

[Negi1984]

Hi All,

I am using below code to save the current file in another location and also want a pop up message if user want to save a copy of file or not ?
but every time I am closing the workbook the message box pop up twice.
Can anybody help me out what mistake I am doing ?

Private Sub Workbook_BeforeClose(Cancel As Boolean)

       Dim MyFile As String

         'On Error GoTo Error_handler:
         
       MyFile = ActiveWorkbook.Name
       ' Do not display the message about overwriting the existing file.
       Application.DisplayAlerts = False
       
Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
    Connection.OLEDBConnection.BackgroundQuery = False
Next Connection
       
       ActiveWorkbook.RefreshAll

       ActiveWorkbook.Save
              

Dim UserAnswer As Long
UserAnswer = MsgBox("Would you like to save file in Network drive ?", vbYesNo, "Save?")

If UserAnswer = vbYes Then
       ActiveWorkbook.SaveAs Filename:="C:\Users\abc\Documents" & MyFile
       ' Close the workbook by using the following.
       
       MsgBox "File Saved in Network Drive"

       ActiveWorkbook.Close
 
Else
      ActiveWorkbook.Save
      ActiveWorkbook.Close

End If
'Error_handler:
'MsgBox "Auto Save Cancelled"

End Sub

 

[DanteAmor]

Try this, I fixed the code and removed the ActiveWorkbook.Close lines

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Dim MyFile As String
  Dim UserAnswer As Long
  Dim Connection As Variant
  MyFile = ActiveWorkbook.Name
  ' Do not display the message about overwriting the existing file.
  Application.DisplayAlerts = False
  For Each Connection In ActiveWorkbook.Connections
    Connection.OLEDBConnection.BackgroundQuery = False
  Next Connection
  ActiveWorkbook.RefreshAll
  ActiveWorkbook.Save
  UserAnswer = MsgBox("Would you like to save file in Network drive ?", vbYesNo, "Save?")
  If UserAnswer = vbYes Then
    ActiveWorkbook.SaveCopyAs Filename:="C:\Users\abc\Documents\" & MyFile
    MsgBox "File Saved in Network Drive"
  Else
    ActiveWorkbook.Save
  End If
End Sub