Cancel printing based on another macro

samerickson89

New Member
Joined
Jun 13, 2019
Messages
38
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top