Very specific invoice auto numbering for the gurus

[VanAlex]

Hi!

My ideia is the following:

Create a template for an invoice that autonumbers it like so:

yyyymmdd_### eg: 20031120_001

The auto numbering method must allow me to have control on the nex
number to be choosen (even though I want it automatic most times,
just want that option so that I can correct wrong invoices);

It must not be registry based because that would loose the numbering i
I opened the file in another PC;

when I create a new invoice from the template the default name for thi
workbook when I save it would be the same as the invoice number;

When I opened a previously created invoice that was allready numbere
it would not receive a new invoice number, that would be messy wheneve
I wanted to print old invoices.


My ideia is that maybe the VBA code could keep track of the number
that were already used by checking either a second sheet or checkin
the filenames a specific folder has (eg. if it recognized the file wit
20031120_040 it would set the invoice number to the new one a
20031120_041)

Either one of this aproaches would allow me to control very easily th
numbering of invoices and would allow me to open the template/folder i
any PC and still keep the correct numbering.

The options to save the workbook with the invoice number is some cod
that specifies the default name should be the same has a specific cell
A9 in this case....

I think that's it for now.....Any gurus wanna take this one =)

 

[Dianne]

One approach:

Create a workbook (not a template). On one of the sheets, create two
named ranges (say LastDate and LastNumber). Hide this sheet. Then create
a button on a visible sheet that says: "Create New Invoice". Your code
behind the button would do something like this:

Check to see if today's date is the same as in the range LastDate. If
not, change LastDate to today's date and change LastNumber to 0. Then
pop up an input box asking for confirmation of the number to be used for
the invoice. This would default to LastNumber + 1.

When the user clicks OK, increment LastNumber, create a new workbook and
change the contents of one of the cells to your date + number format.
Then save and close the first workbook so that it is available to other
users -- note that in order to avoid more than one instance with the
same invoice number, only one person at a time would be able to access
this workbook.

If you base the new workbook on a template, you could have code in the
workbook that, when you saved the workbook it would look at the cell
with the invoice number and use that as a basis for the file name.

--
HTH,
Dianne


In