Hi guys
I'm working on a project to design an invoice that starts numbering by the first day of the month.(i.e. 06060001 for the beginning of june 2006)....can anyone help me to make it restart numbering at the last day of the month keeping in mind the 30,31 days months.....
thanx in advance..
Interesting little problem. Try this attachment which uses the registry to save invoice numbers. By playing around with the day and check values, you can test for restart numbering on any day.
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanx for the effort man ...I think it needs some modifications ...you can have a look to the attachments...waiting for response.
Sorry man ... I attached ur invoice...try again..one more thing if u don't mind..can I hide the "0.00" in the amount column when there no values in the "unit price"and "QTY" cells ......
Last edited by paris_guy; 06-27-2006 at 08:14 AM.
Is 0606 mmdd or ddmm?
To hide "0.00"
=IF(E13<>"",PRODUCT(E13:F13),"")
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Try this
0606 is yymm......the rest are the invoice number(4digits)
I think I didn't explain the full story...sorry guys...
my project is to design an invoice that:
1- protects the main cells
2- starts its first number at day one of the month in the format of yymmxxxx (e.g at july 2006 it'll be "06070001")
3- Calculates the product of the QTY and Unit price without showing the "0.00" if they were blank.
4- Prints 2 copies
5- Increments the invoice number and inputs selected details such as the number, issued to and amount fields into another sheet after (or before)the print
6- Clears fields for new invoice
It can be similar to the one in this article but with some changes http://www.vbaexpress.com/kb/getarticle.php?kb_id=348
Good luck guys...thanx in advance
Save the file as a template and then it does all you want does it not?
yeh man....but how??Originally Posted by xld
is it that complicated??it looks simple but i couldn't do it.....
...can u explain??
</IMG></IMG>
What if you have two invoices for the same day?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Here is a template file.
Load it in your Excel templates directory, then you can invoke it each time as a fresh file from File>New (not the New icon)
the day is not involved in the number only yy and mm. so in a day there can be a number of invoices.
Here's a version with some added automation for you to play with. I've used XLD's numbering code 'cos it's neater than mine was. The clearing and copying, I'll let you attempt. Not too difficult with the macro recorder and a bit of searching.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
mdmackillop.......NICE JOB MAN...I'm Amazed by the capability of the program and your ability to take advantage of it....can u lock the fields "time, invoice numberitem, description, unit price, amount, total& balance" cuz the formula is deleted if u delete the content of the cells.(I tried to protect the sheet but then the drop menu was disabled).
I don't see the problem with protection.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
mdmackillop.....thanxxxxxxxxxxxxxxxxxxx......very helpfull.
mdmackillop... Two more things if u don't mind.........I'd like to add some more features to the invoice....
1- with this version, the invoice number increments only if printed from the PRINT button and NOT if printed from the file menu. can it be done through both?
2- can the invoice be auto saved after the print (to keep the last invoice number)
thanx again and again....
Hi Paris-Guy,
These can both be done, but we need some input from yourself. Post your own attempts to solve these problems and I'll be glad to assist further. The macro recorder and a study of the code should help.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
For my information, can you tell me why you would want the invoice number incremented after/before printing? If you printv the same invoice twice, it gets two different numbers.
Posting Permissions
Reply With Quote
