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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|