Error when using Application.FileDialog(msoFileDialogSaveAs)

Question

I have this code that's used for getting a file ready to save and saving, however it causes Excel to 'encounter an error' and close. I have pinned the error down to the .execute line.

Why does this cause the crash and is there a work around?

Sub Save_close()

    Dim Ws As Worksheet
    Dim Wb As Workbook

    Application.DisplayAlerts = False

    MsgBox "Do you want to Save a new copy", vbYesNo, "Save New"

    If vbYes Then

        Workbooks("A380 Master.xlsm").Sheets("OutPutSheet").Range("C:C", "F:F").Delete

    For Each Ws In ActiveWorkbook.Sheets
        If Not Ws.Name = "OutputSheet" Or Ws.Name = "Sheet1" Then
            Ws.Delete

        ElseIf Ws.Name = "OutputSheet" Or Ws.Name = "Sheet1" Then

        End If
    Next Ws

    With Application.FileDialog(msoFileDialogSaveAs)
        .Show
        .Execute
    End With

    ElseIf vbNo Then

        'Workbooks("A380 Master.xlsm").Sheets("OutPutSheet").Range("A1").Select
    End If

End Sub

do you have any reason to use `FileDialog`? try with [this `Workbook.SaveAs` method](http://msdn.microsoft.com/en-us/library/ff841185%28v=office.14%29.aspx) instead. — Kazimierz Jawor, Aug 09 '13 at 06:12
Two more tips- 1. your `if statement` inside the loop seems to be not logic. 2. when you want to successfully delete all your sheets meeting criteria you should use different kind of loop: `For i= Activeworkbook.Sheets.Count to 1 step -1` and next you will need to change `Ws variable` inside the loop into `Sheets(i)` object reference. — Kazimierz Jawor, Aug 09 '13 at 06:16
Also, it seems you are mixing references to `Workbooks("A380 Master.xlsm")` and to `ActiveWorkBook` : Are these distinct entities ? Else, you might consider referring to it by one single way (i.e. `ThisWorkBook) — d-stroyer, Aug 09 '13 at 06:27
An finally : The result of MsgBox is wrongly checked : `If vbYes` is always `True`. — d-stroyer, Aug 09 '13 at 06:30
the reference to activeworkbook is my bad i tested that code in a new workbook and forgot to put a380 master back in. Thanks for the tips im pretty new to VBA so they help a fair bit with finding better solutions. cheers — Tom D, Aug 09 '13 at 07:01

score 1 · Accepted Answer · answered Aug 09 '13 at 06:36

To summarize the comments above, a solution would be :

Sub Save_close()

Dim Ws As Worksheet
Dim Wb As Workbook
Dim i as Long

Application.DisplayAlerts = False

Set Wb = Workbooks("A380 Master.xlsm")

If (MsgBox("Do you want to Save a new copy", vbYesNo, "Save New")) = vbYes Then

    Wb.Sheets("OutPutSheet").Range("C:C", "F:F").Delete

    For i = Wb.Sheets.Count to 1 step -1
        Set Ws = Wb.Sheets(i)
        ' Delete all except "OutputSheet" and "Sheet1"
        If Not (Ws.Name = "OutputSheet" Or Ws.Name = "Sheet1") Then
            Ws.Delete
    '    ElseIf Ws.Name = "OutputSheet" Or Ws.Name = "Sheet1" Then

        End If
    Next i

    Wb.SaveAs 

Else

    'Workbooks("A380 Master.xlsm").Sheets("OutPutSheet").Range("A1").Select
End If

End Sub

Error when using Application.FileDialog(msoFileDialogSaveAs)

1 Answers1