Exit Sub If Invoice Exists

[JenniferS]

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

 

[Logit]

Not tested here ...

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

 

[JenniferS]

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.

 

[BobBridges]

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:

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?

 

[JenniferS]

Actually could it check the list from the Invoice Payments sheet to see if the invoice number exists?

 

[JenniferS]

The If statement is red color in the VBA.

 

[BobBridges]

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:

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

 

[JenniferS]

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.