End Sub if if xlDialogSaveAs is cancelled

wadergirl

New Member
Joined
Jun 3, 2016
Messages
49
I have a macro-enabled workbook that I'm using as a template.
When the user is done filling in new info daily, they click on a form button at the top that runs this macro, saving the modified workbook as a .xlsx (so the template doesn't get mucked up).

VBA Code:
Sub SaveClose()
    Application.Dialogs(xlDialogSaveAs).Show , sFilename, xlWorkbookDefault 'User defines filename - should be Daily mmddyy.xlsx
    Workbooks("Template w formulas.xlsm").Close SaveChanges:=False 'Closes this workbook without saving
End Sub

Unfortunately, if the user cancels out of the dialog box instead of saving, the code just continues to the second line, closing the template without saving and losing all of the data that was input.

I've tried using
VBA Code:
If Application.Dialogs(xlDialogSaveAs).Show , sFilename, xlWorkbookDefault = False Then Exit Sub
but of course the code does not like the commas in the If statement.

Same with
VBA Code:
    Dim x
    x = Application.Dialogs(xlDialogSaveAs).Show , sFilename, xlWorkbookDefault
    If x = False Then Exit Sub

I feel like this should be so obvious, but I must be missing something.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Does this work?
VBA Code:
Sub SaveClose()
    'User defines filename - should be Daily mmddyy.xlsx
    On Error GoTo errorhandler
    Application.Dialogs(xlDialogSaveAs).Show , sFilename, xlWorkbookDefault
  
    'Closes this workbook without saving
    Workbooks("Template w formulas.xlsm").Close SaveChanges:=False
  
    errorhandler:
                 Exit Sub
End Sub
 
Upvote 0
... but of course the code does not like the commas in the If statement...
Try wrapping the arguments in round brackets, like
VBA Code:
Sub SaveClose()
    If Application.Dialogs(xlDialogSaveAs).Show(sFilename, xlWorkbookDefault) Then 'User defines filename - should be Daily mmddyy.xlsx
        Workbooks("Template w formulas.xlsm").Close SaveChanges:=False 'Closes this workbook without saving
    End If
End Sub
 
Upvote 0
Does this work?
VBA Code:
Sub SaveClose()
    'User defines filename - should be Daily mmddyy.xlsx
    On Error GoTo errorhandler
    Application.Dialogs(xlDialogSaveAs).Show , sFilename, xlWorkbookDefault
 
    'Closes this workbook without saving
    Workbooks("Template w formulas.xlsm").Close SaveChanges:=False
 
    errorhandler:
                 Exit Sub
End Sub

I got no code errors, but the SaveAs Dialog box didn't open up at all.

Try wrapping the arguments in round brackets, like
VBA Code:
Sub SaveClose()
    If Application.Dialogs(xlDialogSaveAs).Show(sFilename, xlWorkbookDefault) Then 'User defines filename - should be Daily mmddyy.xlsx
        Workbooks("Template w formulas.xlsm").Close SaveChanges:=False 'Closes this workbook without saving
    End If
End Sub

This worked! I knew it was something minor. Well, the workbook close part didn't work, but I now realize it's because by saving the template with a new name/file extension, the "template" is no longer open, so I'm solving it with an ActiveWorkbook.Close instead.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top