CODEWITHSUNDEEP
vbaexcel
CC268
CC268

Reputation: 203

xlDialogSaveAs - End ALL code if "cancel" is selected

EDIT: I figured it out myself. I feel pretty silly, but replacing "Exit Sub" with "End" works perfectly.

Background: I have a Sub that uses the "Call" function to run multiple subs within one Sub (see Code #1 below).

Option Explicit

Sub MIUL_Run_All()

Dim StartTime As Double
Dim SecondsElapsed As String

'Remember time when macro starts
  StartTime = Timer

Call OptimizeCode_Begin

Call Format_MIUL
Call Custom_Sort_MIUL
Call Insert_Process_List
Call Format_Process_List

Call OptimizeCode_End

'Determine how many seconds code took to run
  SecondsElapsed = Format((Timer - StartTime) / 86400, "ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

My first code that is called out, "Format_MIUL", prompts the user to save the file, using the following line of code (see Code #2 below). This code works, but the problem is that if the user presses the "Cancel" button, the rest of the code called out in the main sub (Code #1 above) will continue to run. I want ALL code to stop if the user presses the cancel button. I just can't seem to figure out how to do that.

'Save file as .xlsm
MsgBox "       Save as Excel Workbook (.xlsx)!"
Dim userResponse As Boolean

On Error Resume Next
userResponse = Application.Dialogs(xlDialogSaveAs).Show(, 51)
On Error GoTo 0
If userResponse = False Then
Exit Sub
Else
End If

Any help is greatly appreciated.

Upvotes: 1

Views: 1514

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

The Call keyword has been obsolete for 20 years, you can remove it.

The End keyword will effectively end execution, but it's pretty much a big red "self-destruct" button that you effectively never need to use, given properly structured code.

Looks like Format_MIUL is a Sub procedure. Make it a Function and return a Boolean value that tells the caller whether it's ok to proceed, or if the rest of the operations should be cancelled:

Private Function Format_MUIL() As Boolean
    '...
    'Save file as .xlsm
    MsgBox "       Save as Excel Workbook (.xlsx)!"
    Dim userResponse As Boolean

    On Error Resume Next
    userResponse = Application.Dialogs(xlDialogSaveAs).Show(, 51)
    On Error GoTo 0

    'return False if userResponse isn't a filename, True otherwise:
    Format_MUIL = Not VarType(userResponse) = vbBoolean
End Function

And now instead of this:

Call Format_MIUL

The caller can do this:

If Not Format_MIUL Then Exit Sub

And there you go, graceful exit without any self-destruct buttons pressed.

Upvotes: 2

Related Questions