#1
|
|||
|
|||
Exit Module sub code when cancel is selected
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 How do I stop the module code from continuing if I select Cancel? |
#2
|
||||
|
||||
Is Workbook_BeforeSave your own routine, or the built-in workbook event?
|
#3
|
|||
|
|||
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 |
#4
|
||||
|
||||
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.
|
#5
|
|||
|
|||
Thanks for the reply - I will school myself up on 'Function' and then work to improve my coding in this particular project.
|
#6
|
|||
|
|||
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 |