Exit Module sub code when cancel is selected

kiwimtnbkr · #1 04-06-2022, 01:46 AM

I have the following command button code in a module. When the command button is pressed, it calls the Workbook_BeforeSave routine to name the workbook correct before attaching it in an email complete with signature block. No problems - runs sweetly.

Code:

Sub emailrequest()
    Call OrderRequest.Workbook_BeforeSave(True, False)
Dim OutApp As Object
Dim OutMail As Object
Dim signature As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    
    With OutMail
        .To = "xxx@xxx.xxx"
        .Subject = "Order Request"
        .Attachments.Add ActiveWorkbook.FullName
        .display
    End With
 
    signature = OutMail.body
    With OutMail
        .CC = ""
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

When the save as dialog box appears, if I select Cancel, the box disappears but the sub continues to run and attaches the workbook to an email.

How do I stop the module code from continuing if I select Cancel?

Debaser · #2 04-07-2022, 01:50 AM

Is Workbook_BeforeSave your own routine, or the built-in workbook event?

kiwimtnbkr · #3 04-07-2022, 02:56 AM

This is the Workbook_BeforeSave code I am using:

Code:

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
Dim xFileName As String
Dim U7part As String, U8part As String, K13part As String
    If (Range("U7") = Empty) Then
        MsgBox "'Request number' is mandatory.", vbCritical, "text"
        cancel = True
        Range("U7").Select
    End
        Else
    If (Range("U8") = Empty) Then
        MsgBox "'Request date' is mandatory.", vbCritical, "text"
        cancel = True
        Range("U8").Select
    End
        Else
    If (Range("K13") = Empty) Then
        MsgBox "'Requesting Dept' is mandatory.", vbCritical, "text"
        cancel = True
        Range("K13").Select
    End
        Else
    If (Range("L33") = "click here to select") Then
        MsgBox "para. 3.b. 'Method of Delivery' is mandatory.", vbExclamation, "text"
        cancel = True
        Range("L33").Select
    End
        Else
    If (Range("L37") = Empty) Then
        MsgBox "para. 3.d. 'Date required by' is missing.", vbExclamation, "text"
        cancel = True
        Range("K37").Select
    End
        Else
Call RenameTemplateSheet
        U7part = ws.Range("U7").value
        U7part = Replace(Replace(U7part, " / ", "/"), "/", "_")
        U8part = ws.Range("U8").value
        U8part = Replace(Replace(U8part, " / ", "/"), "/", "_")
        K13part = ws.Range("K13").value
        K13part = Replace(K13part, "/", "_")
Range("U9").Select
If SaveAsUI <> False Then
    cancel = True
        xFileName = Application.GetSaveAsFilename(K13part & " text " & Range("A1").value & U7part & " dated " & U8part, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file")
            If xFileName <> "False" Then
            Application.EnableEvents = False
            ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
            Application.EnableEvents = True
        End If
    End If
    End If
    End If
    End If
    End If
End If
End Sub

Thanks heaps to any light you can shed on this....

Debaser · #4 04-07-2022, 04:50 AM

You've already got End sprinkled throughout that code (yuk) that does what you need, so you could simply use that again. IMO there are very few situations where that is really the best option. I'd move the validation code into a separate function that returns True if the save happens and false otherwise, then you can call that from anywhere you need it rather than having to make events public just so that you can call their code directly rather than leaving them to respond to events as they are designed.

kiwimtnbkr · #5 04-08-2022, 12:54 AM

Thanks for the reply - I will school myself up on 'Function' and then work to improve my coding in this particular project.

kiwimtnbkr · #6 04-08-2022, 12:56 AM

Marking as solved - has come up with a way to do what I wanted to do. Would probably make the coding gurus out there roll their eyes but it does what I needed it to do so it will suffice for the time being.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Standard Module, Class Module and UserForm	jpl	Word VBA	0	03-14-2022 07:20 AM
VBA code not in module of new document produced by mail merge	MP1989	Mail Merge	3	09-10-2018 02:16 PM
pause VBA code to view PDF file then continue or cancel	trevorc	Excel Programming	3	06-17-2018 09:46 AM
Inserting code line in Word Module	mihnea96	Excel Programming	2	08-15-2017 04:57 AM
Stop macro if no file is selected in dialog box (when user presses cancel instead of selecting file)	spencerw.smith	Word VBA	2	08-12-2014 07:56 AM