none
Excel stopped working RRS feed

  • Question

  • I had asked several forums about this problem and no one had even offered an answer.

    It is a subroutine Before_Save; I want to save a file with a designated name -

    when I used the [X] at the upper right of the sheet - it worked smoothly - but 

    when I used the Save icon - a prompt "Excel stopped working" always appear - but when ignored

    it goes on to save the file anyway.  What is causing the prompt?

    I tried saving the file with its original name - it worked without the prompt..

    many many thanks

    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


    • Edited by LFKim2018 Monday, August 27, 2018 12:11 AM
    Monday, August 27, 2018 12:08 AM