xlDialogSaveAs makes Excel crash

Question

I wanted to create a small macro that forces the user to use the SaveAs dialog in MS Excel 2010 ("MS Office Professional Plus 2010" in case it makes a difference) instead of just saving the file under the same name. I saved this procedure under the workbook object:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then
        msg = "Use the 'Save As' dialog to save a new version of the file"
        Style = vbOKCancel
        Query = MsgBox(msg, Style)
        If Query = vbOK Then
            Application.Dialogs(xlDialogSaveAs).Show
        ElseIf Query = vbCancel Then
            Cancel = True
        End If
    End If
End Sub

It all works well: I press "Ctrl-S" and get the prompt. I click "OK" and use the SaveAs dialog to save the file under a different name. But as soon as I hit the "Save" button in the dialog, Excel crashes.

I am probably using the xlDialogSaveAs command in a wrong way but I just cannot figure out why this does not work. There are no error messages in the debugger. There is no other VBA code anywhere else in the workbook. I am trying to save the workbook as an .xlsm file (the SaveAs dialog correctly defaults to it).

Is there anyone out there who can help me?

I just tried it (Excel 2013) and it works for me. What version of Excel do you have? What file type are you saving as? Have you tried different file names? Do you have any other code in this workbook? — Michael Russo
Hi Michael, good to know that at least my approach was not entirely wrong...! I am using Excel 2010. Could be that but that would be surprising because I have found that command in a few old forum entries. The SaveAs dialog defaults to ".xlsm" and I keep it as that. All I am doing is changing the name. I have tried different names but to no avail. There is no other code anywhere in the workbook. — khalito
Does excel crash if you just use SaveAs? Does it crash no matter where you try and save it and whatever name you give it? Does it save the file and then crash or crash before it's actually saved? — Michael Russo
@MichaelRusso, your change solved my problem! I changed the If-Then construct as suggested and it stopped crashing! Thank you very much! — khalito

Michael Russo Michael Russo · Accepted Answer · 2016-08-22T15:12:46

Try,

If Query = vbOk Then 
    Application.Dialogs(xlDialogSaveAs).Show 
End If 
Cancel = True.

I suspect there is a problem trying to save in the original call and trying to save in the new dialog you open up

xlDialogSaveAs makes Excel crash

1 Answers