I'm totally uncertain of which of the many Invoice templates you are
referring to. I just opened up Excel 2007 and went to Invoices templates and
there are a dozen or more available. The 3 or 4 that I grabbed and looked at
all had same invoice number: 100, and each one was just simply typed in -
nothing automatic about them at all.

Because I'm unsure which template you're talking about, it's hard to answer
the question in your second paragraph, sorry.

The 'solution' I put up for Pammi J is an on-demand macro coded in VBA.
That way you get a new number when you choose to run the macro, not every
time you open the workbook or when you go to the worksheet.

Now, IF you had a function (VBA code defined as a Function and not as a Sub)
then you could put a formula referring to it in a cell, but here's how that
would work (and you probably wouldn't like it)
if you included the statement
Application.Volatile
in the function code, then EVERY TIME the worksheet was recalculated, the
invoice number would be changed. Now, and this might come close to what you
want - if that line of code was left out of it, then basically it would get
the number one time and never again (I think - that's the way I remember that
type of thing working, but memory could be faulty). However - in either case
you need some place to keep that number so you can grab it and update it for
the next invoice you create. If ALL of your invoices for ALL of your
customers are in the one workbook, then you can figure a way to figure out
which one is the largest invoice number in all of the sheets in your
workbook, OR you can store that rascal out somewhere like in a .txt file, so
that other workbooks can pick up on it and not duplicate invoices when you
use multiple workbooks to keep up with your customer invoicing.

As for activating/updating something like that based on an event like
opening the workbook or activating a sheet, that's what the various Workbook_
and Worksheet_ event routines are there to be used for. The developer gets
to pick and choose where to put the code (as I chose neither of the above,
and just put it in a regular non-event-associated Sub type of macro) based on
the requirements for functionality of the workbook or that feature.

I hope that sheds some light on things for you.

Things you have to keep in mind:
If I have a routine that works everytime a workbook is opened, without
restraint, then it's going to do that. So if the routine says "take the
value in A1 and increment it by 1 and stuff it back into A1" then every time
that workbook is opened, that value is going to increment, even if you really
didn't want it to.

If you have a routine that says go find the largest invoice number used in
the past and increment it by one when I activate (go to) a sheet and stick
that into cell A1 on that sheet, then the numbers are going to change on each
sheet each time you take a look at it. Now, if you restrain those with some
code that says "if there's already a value in A1, don't do anything, but if
there is not, then find the largest invoices previously used and add 1 to
that and put it into A1" then you're pretty stable in your invoice numbering,
but you have to figure out where to keep that last invoice number used; that
can either be in the same workbook (you could even figure it out by looking
at A1 of each sheet in code and saving the largest number found) or in a
central location in the workbook to be picked up and used and recorded for
later use.

But again it's all about control and implementation of such a feature. And
that's what this exercise is all about. Again, this is an area that Excel is
weak (to the point of starvation) in - it has no built in ability to deal
with something like this, and it's up to the developer to design and
implement the solution based on the requirements. There are probably 500
ways to do this. We've only looked at one or two in this thread. Heck, I
can envision someone writing a routine that worked through the Worksheets
collection and found the sequence in it that a sheet is in it and using that
sequence number as the invoice number to put on it. But there are problems
there if you delete sheets or add ones that aren't to be invoice sheets.

"lizard1socal" wrote:


--
To JLatham and Bill Ridgeway,
It seems that you both know a couple methods that will activate the
auto numbering in an invoice, but which one works in what area of the program
is the question !
My curious is if you call up the basic invoice template and enter the
code or formula into the function/format /formula bar for the cell that the
templates invoice number is displayed in, and save the sheet, wouldnt that
activate that cell to do the autonumbering each time the blank template is
opened ??
Just trying to understand you, without drawing me
pictures !

TX, Lizard1socal