Sequential invoice numbers on multiple templates

[imported_unknown]

I have 8 different invoices that I use on a daily bases. I have created templates for each that when saved, will send the data to a single database and save the invoice to a a specific folder using the invoice number as the file name. What I have haven't figured out yet is how to get an automactic sequential invoice number on the forms. Aslo if I use for example invoice 2222 on form 1, how can form 2 recognize the last number used in form 1 in order to use 2223 and so on skipping from form to form.

 

[Mr_Adams]

Tom

No VBA needed this is the formula i used in each of the Invoive number cells

=MAX('[Finance Invoice Ledger.xls]Dept Ledger'!$A:$A)+1

Works perfectly. Thank you for your help

 

[Mr_Adams]

I just realized that the formula I used created another problem.

=MAX($A:$A)+1

When the template (.xlt) is saved as an .xls it still retains the formula. So if I open the saved Invoice it changes the invoice again. (not good)

I need a VBA that would revome the formula and replace it with the result of the formula.
Or maybe a VBA that uses the formula above along with the VBA removal when saved that Tom used in his suggestion.
This way when the saved invoice is opened for review it still maintains its invoice number.

Any suggestions would be appreciated

 

[Legacy 98055]

<pre>
Hi Mr_Adams.
VBA is not needed if there is a third source for determining your invoice numbers,
which I did not realize. I was going on the premise of comparing values from an
independant, third source seeing that templates, by their very nature, cannot be
saved with changes. I sent you a fix which I tested but am, personally, always in
favor of a solution using native functions if possible. This small bit of code may
do in your "Workbook_Open" procedure. This will test the "type" of Excel file and
change the cell holding the invoice number from a function to a value if the file
type is not a template.

Private Sub Workbook_Open()
If Not ActiveWorkbook.FileFormat = xlTemplate Then
Sheet1.Range("V4").Value = Sheet1.Range("V4").Value
End If
End Sub

</Pre>

Tom

 

[Mr_Adams]

FINAL SOLUTION

Thank you Tom for all the time you put into this solution. It is greatly appriciated

Solution is based on using a template to create an invoice with a with sequential incoice numbers. Also, you must be using a worksheet database.

Use this formula in the cell where you want the invoice number to appear.
assuming your invoice numbers are in column A of your data base

=MAX(A:A)+1

From Excel's main window, right-click the
Excel icon located immediately left of the "File" menuitem,
choose "View Code", paste in as is.

Private Sub Workbook_Open()
If Not ActiveWorkbook.FileFormat = xlTemplate Then
Sheet1.Range("V4").Value = Sheet1.Range("V4").Value
End If
End Sub

Replace "V4" with the location your invoice number cell

If you are using multiple invcoie templates like myself you need to follow these steps for each template.

Works Great with no problems
This message was edited by Mr_Adams on 2002-10-09 16:33