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.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
... 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
 

wadergirl

New Member
Joined
Jun 3, 2016
Messages
49
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,842
Messages
5,627,203
Members
416,229
Latest member
mohammadmihdi

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
Top