Wouldn't the Workbook_Open() event method increment the invoice number in E5
each time you opened the book, i.e., File | Open. And that leads to the
problem of controlling when it increments:

You have a template file .xlt with 0 in E5 you create a new invoice from it
and set invoice number to 1, fill it in and send it out. Later you have to
open up the .xls file and suddenly you realize that the invoice number is 2
-- but darn it, someone else is already using invoice 2 for another sale in
another .xls file. You close your file and open it back up and suddenly it's
invoice #3 in E5.

Somehow you have to control when the invoice number is incremented and when
it isn't. Now, you could modify the Workbook_Open() event to NOT increment
the number if some other information is on the workbook that isn't there in
the 'template' file. Lets say you put the customer name in cell B2, but in
the template that cell is empty. Here's what you'd have to do in the
Workbook_Open() event:
check and see if cell B2 has anything in it, and if it does, don't increment
the invoice number, but if it is empty, then:
increment the Invoice Number in E5
AND
save the 'template file' back out so that the next time you use it, it knows
what the last used invoice number was, and
THEN
give it a new name to be saved under with the new invoice number carved in
stone, so to speak.

This is where the .txt file that McGimpsey uses comes in so handy - the last
invoice number is saved in it, so you don't have to figure out how to do all
of that save, rename, save again type of operation.

It's not just a question of the chicken and the egg, but an added element of
how to tell when the egg will hatch is kind of thrown into it all.

I'm going back and looking at what PammiJ wrote in her last entry here and
see if I cannot work something out based on earlier work plus her added
information.

My question to Bill Ridgeway (not meaning any disrespect or anything like
that at all) is: having come up with a new invoice number fromt he cells in
A1:A999, how do you get that new number into that list? This implies, to me
at least, that somewhere there is a manual operation of entering the last
used invoice number somewhere, and so it's not a totally automatic process,
and is still susceptible to human error (forgetting to update the list of
used invoice numbers).

To give credit to all: it is actually a rather complex issue based on a very
simple need, made so primarily just by the way Excel works.

"Rookie 1st class" wrote:

This works sort of

Private Sub Workbook_Open()
Range("E5") = Range("E5") + 1
End Sub

It does what you want if you open the *.xlt original.
Not if you open a *.xls copy (<File., <New).
Where do I find the McGimpseys code page you are copying from?
Maybe we can both learn something.
Lou

"Bill Ridgeway" wrote:

Assuming Invoice numbers are stored in cells A1 through A99, the formula -
=MAX(Data!$A$1:$A$999)+1
will give you the next available Invoice number.

Regards.

Bill Ridgeway
Computer Solutions