Cancel printing based on another macro

samerickson89

New Member
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:

Code:
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
Code:
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

Well-known Member
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

Rich (BB 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

Rich (BB code):
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
 
Last edited:

samerickson89

New Member
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.

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

dmt32

Well-known Member
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

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = Not Check_Fields
End Sub
Dave
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top