Disallow printing if amount of check request exceeds amount

michelle cox

New Member
Joined
Sep 26, 2003
Messages
2
How can I disallow a check request to print if a certain section is not completed and amount exceeds a certain dollar amount? With the efforts of a macro or VB code, I know this can be accomplished!! Please advise.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Re: Disallow printing if amount of check request exceeds amo

There is a workbook level BeforePrint event which allows you to cancel the print job. A simple piece of code would be to disallow a print if cell A1 of the active sheet is not equal to 1-

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

If Range("A1") <> 1 Then
    MsgBox "Please place a 1 in cell A1 first!", vbCritical
    Cancel = True
End If

End Sub

The above code would need to be placed in the ThisWorkbook module and obviously tailored to your needs. If you have trouble then pleae repost in this thread with your exact requirements.
 
Upvote 0
Re: Disallow printing if amount of check request exceeds amo

Sure can

Put this in the workbook_beforeprint()

if [put what you want to check as being completed here] then
else
cancel = true
end if

end sub

For example if you only want to pring if A1 is greater than 5

Put this in the workbook_beforeprint()

if A1 > 5 then
else
cancel = true
end if

end sub


Hope this helps

Jacob
 
Upvote 0
Re: Disallow printing if amount of check request exceeds amo

You were to fast Mudface :(

:cool:
 
Upvote 0
Re: Disallow printing if amount of check request exceeds amo

Thanks Everyone! What a great prompt response. Well, I tried what Mudface suggested and got an error. Check this out...

Private Sub Workbook_BeforePrint()
'This macro will not allows request to print without completed cells
If Range("f16") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT MANAGERIAL APPROVAL - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("f10") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT NAME OF REQUESTOR - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("f12") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT PURPOSE OF CHECK REQUEST - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("f14") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT DATE OF APPROVAL - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("B8") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT NAME OF PAYEE - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("B10") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("B12") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT CITY ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("B14") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT STATE ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("B16") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT ZIP ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
If Range("C24:K24") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT DEBIT AMOUNT - PLEASE COMPLETE", vbCritical
Cancel = True
End If

End Sub

The error I received was a Compile Error: Procedure declaration does not match description of event or procedure having the same name.

Got any ideas?
 
Upvote 0
Re: Disallow printing if amount of check request exceeds amo

You have to put an End If to close all the Ifs

Like

Thanks Everyone! What a great prompt response. Well, I tried what Mudface suggested and got an error. Check this out...

Private Sub Workbook_BeforePrint()
'This macro will not allows request to print without completed cells
If Range("f16") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT MANAGERIAL APPROVAL - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("f10") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT NAME OF REQUESTOR - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("f12") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT PURPOSE OF CHECK REQUEST - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("f14") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT DATE OF APPROVAL - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("B8") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT NAME OF PAYEE - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("B10") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("B12") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT CITY ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("B14") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT STATE ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("B16") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT ZIP ADDRESS - PLEASE COMPLETE", vbCritical
Cancel = True
end if

If Range("C24:K24") <> 1 Then
MsgBox "CHECK REQUEST WILL NOT PRINT WITHOUT DEBIT AMOUNT - PLEASE COMPLETE", vbCritical
Cancel = True
End If

End Sub


also the last if where you have range("C24:K24") <> 1 this will not work If this is a merged cell just put "C24"

Hope this helps

Jacob
 
Upvote 0
Re: Disallow printing if amount of check request exceeds amo

As well as Jacob's suggestions, your event procedure should begin with-

Private Sub Workbook_BeforePrint(Cancel As Boolean)

not

Private Sub Workbook_BeforePrint()

and it should be placed in the ThisWorkbook module.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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