• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Exit Sub If Invoice Exists

JenniferS

Member
Hello,

On the Invoice Template sheet there is a shape "Save" which runs code to save the invoice as PDF and posts the information on the "Invoice Payments" sheet.

Is there a way to exit the code if the invoice already exists? Also popup message saying "Invoice has already been saved, nothing was done!"

Thank you
 

Attachments

  • Invoice Inventory Template PDF Only Rev 1.1.xlsm
    65.2 KB · Views: 6
Not tested here ...

Code:
Sub SaveAsPDF()

path = ActiveWorkbook.Sheets("Invoice Template").Range("M11")

invno = Range("H4")
custlastname = Range("D11")
amt = Range("H34")
dt_issue = Range("H6")
term = Range("H7")
fname = invno & " - " & custlastname



    If Len(Dir(fname, vbNormal)) > 0 Then
        MsgBox "File already exists, exiting...", vbExclamation, "File Exists"
        Exit Sub
    End If



ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
IgnorePrintAreas:=False, _
Filename:=path & fname
 
Thanks but it still allows duplicates on the Invoice Payments sheet. It also saves as PDF.

I was looking for a way to not have a duplicate invoice created and recorded.

This way only one copy of each invoice will be saved as PDF and only one line item on the Invoice Payments sheet for each Invoice.
 
I'm not sure what logit thought you'd asked for. Seems to me you need something that checks invno against the list of invoices already recorded in the "Invoice Payments" worksheet, something like this:
Code:
invno = Range("H4")

' Check for duplicate invoice number.
If /* invno already exists in 'Invoice Payments'!C */ Then
  MsgBox "Invoice number " & invno & "is already recorded on row " & invrow."
  Exit Sub
  End If

' Continue.
custlastname = Range("D11")
amt = Range("H34")
dt_issue = Range("H6")
term = Range("H7")
fname = invno & " - " & custlastname
So how do you check invno against those already-recorded numbers? Can you see how, or do you need more help?
 
That's the idea, good. There are a couple ways to do that. There's the Range.Find method, but I've sometimes found that confusing to use in the past; your mileage may vary. You can have your program look down column C in that worksheet, row by row, looking for a match; that's simple, but if you think you're going to have hundreds or thousands of invoices on that sheet then you'll notice it's pretty slow. You can do the same thing by loading that column into an array and checking it there, which is blindingly fast but more work programming-wise.

The easiest way might be this: Use WorksheetFunction.MATCH; feed it the proposed invno and check against col C in the target worksheet. If that call produces an error (no match found), then your new invoice number is unique.

Hey, better yet: In the template worksheet, put the MATCH function right next to the invoice number in col 9; if it finds a match, col 9 gets a big red X, or "dup" or something, to alert the operator even before hitting the button that that invoice is already taken:
Code:
=IF(ISERROR(MATCH(RC8,T_INV[Invoice No.],0)),"","X")
Then in your program all you have to do is check the contents of I4 to see whether it's an 'X' or not.
 
Actually I like the formula method to check if it exists. Thank you very much.

You are very creative coming up with that solution Mr. Bridges.
 
Back
Top