Cancel printing based on another macro

[samerickson89]

I've got a subroutine 'Print_Report' that calls another subroutine 'Check_Fields' and basically, if certain cells are empty then 'Check_Fields' exits the subroutine, but 'Print_Report' continues to run. How can I make one subroutine exit based on something in another subroutine? I tried using end, but I also want the ability to cancel the 'BeforePrint' event if need be. Here's my code so far:

Sub Print_Report()

Call modCheck.Check_Fields

Dim Today As String
Dim OutName As String
Today = Format(Date, "yyyymmdd")
OutName = "FCAD_ECR_" & Today

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ActiveWorkbook.Path & "\Filled Out ECR's\" & OutName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    to:=1, _
    OpenAfterPublish:=True
    
End Sub

Sub Check_Fields()

If Range("E4").Value = "" Then
    MsgBox "Field 'Requested by' is mandatory"
    Exit Sub
End If
 
If Range("E11").Value = "" Then
    MsgBox "Field 'Reason for change' is mandatory"
    Exit Sub
End If
 
If Range("E26").Value = "" Then
    MsgBox "Field 'Where was the issue identified?' is mandatory"
    Exit Sub
End If

End Sub

 

[dmt32]

Hi,
make your Check_Fields Sub a Function where you can return a boolean value (True / False) to confirm the status of the check in the calling sub

Update code

Function Check_Fields() As Boolean
    Dim Cell As Range
    Dim i As Integer
    
    For Each Cell In Range("E4,E11,E26")
        i = i + 1
        Check_Fields = CBool(Len(Cell.Value) > 0)
        If Not Check_Fields Then
            MsgBox "Field " & Choose(i, "'Requested by'", _
                                        "'Reason for change'", _
                                        "'Where was the issue identified?'") & _
                                        " is mandatory", 48, "Mandatory Entry"
            Cell.Select
            Exit Function
        End If
    Next Cell
End Function

And calling Sub

Sub Print_Report()
    Dim Today As String
    Dim OutName As String
    
    If Not Check_Fields Then Exit Sub
    
    '
    'REST OF CODE


    
End Sub

If the called Function returns False ( all fields not completed ) your Print_report Sub will be exited.

Dave

 

[samerickson89]

Hi,
make your Check_Fields Sub a Function where you can return a boolean value (True / False) to confirm the status of the check in the calling sub

If the called Function returns False ( all fields not completed ) your Print_report Sub will be exited.

Dave

That did the trick, thanks! I'd heard that method mentioned elsewhere, but I think seeing an example really helped. Pretty sure I just had the syntax of the function wrong when I tried it before, so I went back to trying to make it work as a sub.

Side note: I was also able to use it to cancel normal printing with what I'm sure is some pretty trivial code to most people here, but I'll share it anyway in case any other noobs happen upon this post.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Check_Fields = False Then
    Cancel = True
End If
End Sub

 

[dmt32]

Hi,
Glad solution helped you.
Functions are very useful & as you discovered, can be used many times in your project which saves repeating code

another way to write you BeforePrint Code

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = Not Check_Fields
End Sub

Dave