Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very specific invoice auto numbering for the gurus
Hi! My ideia is the following: Create a template for an invoice that autonumbers it like so: yyyymmdd_### eg: 20031120_001 The auto numbering method must allow me to have control on the nex number to be choosen (even though I want it automatic most times, just want that option so that I can correct wrong invoices); It must not be registry based because that would loose the numbering i I opened the file in another PC; when I create a new invoice from the template the default name for thi workbook when I save it would be the same as the invoice number; When I opened a previously created invoice that was allready numbere it would not receive a new invoice number, that would be messy wheneve I wanted to print old invoices. My ideia is that maybe the VBA code could keep track of the number that were already used by checking either a second sheet or checkin the filenames a specific folder has (eg. if it recognized the file wit 20031120_040 it would set the invoice number to the new one a 20031120_041) Either one of this aproaches would allow me to control very easily th numbering of invoices and would allow me to open the template/folder i any PC and still keep the correct numbering. The options to save the workbook with the invoice number is some cod that specifies the default name should be the same has a specific cell A9 in this case.... I think that's it for now.....Any gurus wanna take this one =) ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Very specific invoice auto numbering for the gurus
One approach:
Create a workbook (not a template). On one of the sheets, create two named ranges (say LastDate and LastNumber). Hide this sheet. Then create a button on a visible sheet that says: "Create New Invoice". Your code behind the button would do something like this: Check to see if today's date is the same as in the range LastDate. If not, change LastDate to today's date and change LastNumber to 0. Then pop up an input box asking for confirmation of the number to be used for the invoice. This would default to LastNumber + 1. When the user clicks OK, increment LastNumber, create a new workbook and change the contents of one of the cells to your date + number format. Then save and close the first workbook so that it is available to other users -- note that in order to avoid more than one instance with the same invoice number, only one person at a time would be able to access this workbook. If you base the new workbook on a template, you could have code in the workbook that, when you saved the workbook it would look at the cell with the invoice number and use that as a basis for the file name. -- HTH, Dianne In , VanAlex typed: Hi! My ideia is the following: Create a template for an invoice that autonumbers it like so: yyyymmdd_### eg: 20031120_001 The auto numbering method must allow me to have control on the next number to be choosen (even though I want it automatic most times, I just want that option so that I can correct wrong invoices); It must not be registry based because that would loose the numbering if I opened the file in another PC; when I create a new invoice from the template the default name for this workbook when I save it would be the same as the invoice number; When I opened a previously created invoice that was allready numbered it would not receive a new invoice number, that would be messy whenever I wanted to print old invoices. My ideia is that maybe the VBA code could keep track of the numbers that were already used by checking either a second sheet or checking the filenames a specific folder has (eg. if it recognized the file with 20031120_040 it would set the invoice number to the new one as 20031120_041) Either one of this aproaches would allow me to control very easily the numbering of invoices and would allow me to open the template/folder in any PC and still keep the correct numbering. The options to save the workbook with the invoice number is some code that specifies the default name should be the same has a specific cell, A9 in this case.... I think that's it for now.....Any gurus wanna take this one =) ? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
execute auto invoice numbering in excel file | Excel Discussion (Misc queries) | |||
Numbering Invoice | Excel Discussion (Misc queries) | |||
is there an auto-numbering tax invoice templete for excel | Excel Discussion (Misc queries) | |||
Is auto invoice numbering possible in Excel 2003? | Excel Discussion (Misc queries) | |||
Invoice Numbering | Excel Worksheet Functions |