Home |
Search |
Today's Posts |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trying to do a auto number generater | Excel Worksheet Functions | |||
Generate Auto Order Number | Excel Discussion (Misc queries) | |||
Auto Number | Excel Discussion (Misc queries) | |||
Auto number invoices in Excel | Excel Worksheet Functions | |||
assign auto number and auto date | Excel Discussion (Misc queries) |