A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Auto Number in Invoice



 
 
Thread Tools Display Modes
  #21  
Old January 9th, 2007, 05:46 AM posted to microsoft.public.excel.newusers
lizard1socal
external usenet poster
 
Posts: 10
Default Auto Number in Invoice

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal



  #22  
Old January 9th, 2007, 06:49 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 3,017
Default Auto Number in Invoice

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

  #23  
Old January 9th, 2007, 06:50 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 3,017
Default Auto Number in Invoice

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal



  #24  
Old January 9th, 2007, 09:13 PM posted to microsoft.public.excel.newusers
Rookie 1st class
external usenet poster
 
Posts: 108
Default Auto Number in Invoice

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (File, New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal



  #25  
Old January 10th, 2007, 01:12 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 3,017
Default Auto Number in Invoice

Would you like me to go back through the method you were investigating and
check anything out? I suspect that the way you describe it as working may be
by intent - create a new file from the template, set up what ever individual
info is needed to get started, close it down and then next time you open it,
it starts incrementing? That kind of operation would be ok for a setup where
you were just printing the created invoice, filling in unique information
each time to print out a new one. The auto-incrementing of it on each open
would be troublesome if you were also trying to keep copies of the invoices
as part of your workbook.

"Rookie 1st class" wrote:

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (File, New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal



  #26  
Old January 10th, 2007, 04:50 AM posted to microsoft.public.excel.newusers
lizard1socal
external usenet poster
 
Posts: 10
Default Auto Number in Invoice

I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal



  #27  
Old January 10th, 2007, 05:31 AM posted to microsoft.public.excel.newusers
JLatham
external usenet poster
 
Posts: 3,017
Default Auto Number in Invoice

We probably scared her off, or she felt the thread was hijacked with
questions you asked, or she may have gone off-line like you and I did, but
with Rookie 1st class, to deal with it all one-on-one.

By now you should have the two working invoices with semi- and fully-
automatic numbering. Perhaps some feedback on those once you've had a chance
to evaluate them might help. If it turns out you think those are handling
things properly, perhaps Pammi J would like to look at them also.

"lizard1socal" wrote:

I AM WONDERING WHAT HAPPENED TO PAMMYJ. DID SHE GET HER PROBLEM RESOLVED OR
FALL OFF THE PLANET ? PAMMYJ YOU OUT THERE ?? WHAT WERE YOUR RESULTS ??
LIZARD1SOCAL
--
lizard1socal



  #28  
Old January 10th, 2007, 08:14 AM posted to microsoft.public.excel.newusers
lizard1socal
external usenet poster
 
Posts: 10
Default Auto Number in Invoice

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"
  #29  
Old January 10th, 2007, 12:08 PM posted to microsoft.public.excel.newusers
Pammi J
external usenet poster
 
Posts: 42
Default Auto Number in Invoice

Rookie 1st class
your solution does work but only if you open the same as last invoice - it
doesnt work off just the original template. which is a shame i thought we had
got it sorted then. I need to be able to open a clean invoice from template
each time (other than the incrementing invoice no) as i have to add customer
details to each one.
im going to have a play around with JLatham zip file now and will let you
know how i get on.

"JLatham" wrote:

Would you like me to go back through the method you were investigating and
check anything out? I suspect that the way you describe it as working may be
by intent - create a new file from the template, set up what ever individual
info is needed to get started, close it down and then next time you open it,
it starts incrementing? That kind of operation would be ok for a setup where
you were just printing the created invoice, filling in unique information
each time to print out a new one. The auto-incrementing of it on each open
would be troublesome if you were also trying to keep copies of the invoices
as part of your workbook.

"Rookie 1st class" wrote:

J, Liz & Pam My method works if not a template or you reopen the template
(negates purpose of template). You must open the document for it to add a 1.
Opening a copy (File, New) of a Template does NOT open the original
document. Therefore it never increments. I suspect that is also true of
McGimpsey link. Dave HELP!!!
Lou

"JLatham" wrote:

Grab the .zip file I uploaded for Pammi J he
http://www.microsoft.com/office/comm...c-bd3aeb241c30

that will give you one way to do it. You can change the reference to cell
E5 in the code to whatever cell on a worksheet you'd like the Invoice number
to show up in. By revising the InvoiceNumber.txt file, you can even set your
own starting number for your first invoice.

"lizard1socal" wrote:

JLantham or somebody..........
Could somebody ,anbody answer my last post , por favor? just want to know
how to auto number an invoice !!??
lizard1socal
--
lizard1socal



  #30  
Old January 10th, 2007, 01:07 PM posted to microsoft.public.excel.newusers
Pammi J
external usenet poster
 
Posts: 42
Default Auto Number in Invoice

Hi yeah im still around lol
Rookie 1st class yours worked but it would only open a new invoice number
off the last invoice used - i need to work from a blank template so i can
enter customer details myself on each new invoice.
JLatham i have just unzipped your files - iv had a play around with the code
but its not working. BUT i have managed to paste my inv template over the top
of yours - leaving the `click here to get new invoice number` button - iv set
it to not print that. Now this does work (as far as iv tested) - now im
looking at getting that code to run on opening of the workbook.


"lizard1socal" wrote:

WELL I GOT THE TWO FORMS TO TRY FROM JLATHAM AND I MUST PUBLICLY THANK HIM
FOR TAKING HIS TIME AND HIS UNDERSTANDING AND HIS KNOWLEDGE AND TIME TO
CREATE THESE FORMS. HE IS A GOOD PERSON AND WILLING TO HELP US OF LESSOR
KNOWLEDGE AND HOW HE HAS THE PATIENCE TO DEAL WITH ME AND MY LACK OF
UNDERSTANDING IN THIS AREA IS A CREDIT TO HIS GREATNESS AS A PERSON. MY
ADMIRATION AND MUCH APPRECIATION GOES OUT TO YOU MR. JLATHAM. THANK YOU VERY
MUCH SIR. !

PS: PROBABLY TO BE CONTINUED DUE TO MASS CONFUSION !! LOL JERRY
--
lizard1socal


"

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.