Excel VBA save code causing multiple save dialogs

Question

I have been trying to find a way to set up the default save options for a specific excel spreadsheet where a default file location is opened, a suggested file name appears in the dialog box and the default file type is a macro enabled workbook(*.xlsm).

I have complied the following two solutions from research; however, both seem to give me a problem where three save dialog boxes open after each other.
Specifically, when the save button is clicked, the save dialog box opens in the correct location (“My Documents/exceltests”) and with the correct file name (“AAAA”). I then click the save button which prompts another save dialog box to open with the exact same properties. After I click save on this dialog box, the workbook is saved in the correct location. However, a third dialog box also opens back in the default location of “My Documents”.

Code Sample 1

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
With Application.FileDialog(msoFileDialogSaveAs)


.InitialFileName = "C:\My Documents\exceltests\AAAA"
 .FilterIndex = 2

 If .Show Then
     ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled
End If
End With

End Sub

Code Sample 2

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)

    End Sub
    Dim fn


fn = Application.GetSaveAsFilename(InitialFileName:="C:\My Documents\exceltests\AAAA", _
fileFilter:=" Excel Macro Enabled Workbook (*.xlsm), *.xlsm,")

If fn <> False Then
ActiveWorkbook.SaveAs Filename:=fn, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End If

If anyone has any clues as to why this is happening and/or how it can be fixed I would be very grateful.

Sixthsense · Accepted Answer · 2016-03-04 07:41:48Z

1

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Application.FileDialog(msoFileDialogSaveAs)
    Application.EnableEvents = False
    .InitialFileName = "C:\My Documents\exceltests\AAAA"
    .FilterIndex = 2
    If .Show Then
        ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled
    End If
    Application.EnableEvents = True
End With

Cancel = True

End Sub

answered Mar 4, 2016 at 7:41

Sixthsense

1,9612 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yaz1069 Over a year ago

Thanks! This almost works. It seems to have got rid of the second dialog box; however, the third one (showing the default My Documents) still comes up. So only two dialog boxes now. Any ideas as to why this is happening?

Collectives™ on Stack Overflow

Excel VBA save code causing multiple save dialogs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related