Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Aug 13, 2018

Excel stopped working

The SUB below is working fine when [X] is clicked BUT not when the SAVE Icon is..

a prompt "Excel stopped working" always pops up when saving but when ignored, it continued  saving anyway.  What could be causing this?

many many thanks

 

The SAVE Icon:

 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim file_name As Variant
Dim FName As String
On Error Resume Next
Application.DisplayAlerts = False
nyr = Format(Sheets("FS").Range("A2"), "yyyy")
nfty = " for the year " & nyr & ".xlsm"
FName = Replace(ThisWorkbook.FullName, ".xlsm", "") & nfty
file_name = Application.GetSaveAsFilename(FName, _
FileFilter:="Excel Files,*.xlsm,All Files,*.*", _
Title:="Save As File Name")
If file_name = False Then
Cancel = True
Else
If LCase$(Right$(file_name, 5)) <> ".xlsm" Then
file_name = file_name & ".xlsm"
End If
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=file_name
Application.EnableEvents = True
End If
On Error GoTo 0
Application.DisplayAlerts = True
Application.Quit
End Sub

 

  • Kenton Smith's avatar
    Kenton Smith
    Copper Contributor

    "On Error Resume Next" is poor coding and should be avoided as much as possible.

    Remove your "On Error Resume Next" statement from your code, and get the proper error. Now you can deal with these errors or manage them via err.number. 

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor

      Mr. Smith

      thank you for your reply.

      I deleted the "on error resume next" -

      still the same prompt popped = [Microsoft Excel stopped working]..

      I ignored it and it saved the file anyway.

      no error number was quoted.

      what could be causing the prompt?

      many thanks

       

       

       

Resources