Disallow printing if amount of check request exceeds amount :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Disallow printing if amount of check request exceeds amount
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

michelle cox
Welcome to the Board


Joined: 26 Sep 2003
Posts: 2

Flag: Blank

Status: Offline

 Reply with quote  

Disallow printing if amount of check request exceeds amount

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.

Post Fri Sep 26, 2003 7:08 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3140
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

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.

Post Fri Sep 26, 2003 7:15 pm 
 View user's profile Send private message

DRJ
Board Master
Board  Master


Joined: 18 Feb 2002
Posts: 1022
Location: California
Flag: Usa

Status: Offline

 Reply with quote  

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

Post Fri Sep 26, 2003 7:16 pm 
 View user's profile Send private message Send e-mail

DRJ
Board Master
Board  Master


Joined: 18 Feb 2002
Posts: 1022
Location: California
Flag: Usa

Status: Offline

 Reply with quote  

Re: Disallow printing if amount of check request exceeds amo

You were to fast Mudface icon_frown.gif

icon_cool.gif

Post Fri Sep 26, 2003 7:18 pm 
 View user's profile Send private message Send e-mail

michelle cox
Welcome to the Board


Joined: 26 Sep 2003
Posts: 2

Flag: Blank

Status: Offline

 Reply with quote  

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?

Post Fri Sep 26, 2003 8:51 pm 
 View user's profile Send private message

DRJ
Board Master
Board  Master


Joined: 18 Feb 2002
Posts: 1022
Location: California
Flag: Usa

Status: Offline

 Reply with quote  

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

Post Fri Sep 26, 2003 9:20 pm 
 View user's profile Send private message Send e-mail

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3140
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

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.

Post Sat Sep 27, 2003 7:15 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.