'Excel spreadsheet closing after saving / crash
It has been a while i'm blocked with vba code running when event occured.
For a brief explanation, before the user save the file a userform pop-up in order to select the name for the file and the version. When he click continue, the getsaveasfilename start and the file will save in the proper location. Nevertheless, when saving the workbook crash and excel close all the file.
Please see below the code :
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sfile, mbox As Variant, cell_path As String
dialog:
save_file.Show
cell_path = ActiveWorkbook.Sheets("ENGINE").Range("H1") & " CAPEX " & ActiveWorkbook.Sheets("ENGINE").Range("I1")
sfile = Application.GetSaveAsFilename(cell_path, "Excel Macro Files, *.xlsm")
If sfile = False Then
Cancel = True
Exit Sub
End If
If Dir(sfile) = "" Then
Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.SaveAs sfile
Application.DisplayAlerts = True
Exit Sub
Else: mbox = MsgBox("The file already exist, do you want to overwrite ?", vbYesNo, "WARNING")
If mbox = vbYes Then
Application.DisplayAlerts = False
Application.EnableEvents = False
ActiveWorkbook.SaveAs sfile
Application.DisplayAlerts = True
Else: GoTo dialog
End If
End If
Application.EnableEvents = True
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
