Disallow printing if amount of check request exceeds amount

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Disallow printing if amount of check request exceeds amount

  1. #1
    New Member
    Join Date
    Sep 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  3. #3
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  4. #4
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Disallow printing if amount of check request exceeds amo

    You were to fast Mudface


  5. #5
    New Member
    Join Date
    Sep 2003
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?

  6. #6
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com