Forum Discussion
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 SmithCopper 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 KimBronze 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
- Kenton SmithCopper Contributor
Also remove "Application.DisplayAlerts = False"
- Lorenzo KimBronze Contributor