Excel- How to make it Automatically increment the invoice number by one ?

I need a simple Invoice that automatically increments the invoice number by one...I have found a few answers about codes in text files etc. but I have not a clue how to do any of this....Does anyone have such an invoice template handy ? I need it to add one to the invoice number everytime it opens up.
LVL 1
AiricRAsked:
Who is Participating?
 
jwleonard1Connect With a Mentor Commented:
I have no way to send the file back to you, so I will provide a simple piece of code for you to use.  All you need to do is copy it out of this post by highlighting, right clicking and selecting copy, open the workbook, press ALT + F11, then right click in the white space and select paste.  Then you would save and close in the VB editor and save/close and reopen the workbook.  Here is the code:

Sub Workbook_Open()

Sheets("Simple Invoice").Range("C5") = _
        Sheets("Simple Invoice").Range("C5") + 1

End Sub

This will do exactly what you asked for, however it will always change the invoice number even if you only open the file for a quick look, this will cause it to skip invoice numbers.  If that is acceptable then great, if not we can have it update the invoice number as soon as print is selected, this way it would only update just before printing.  Let me know if that works better.

Lastly, If you would like we can create a hot key so if you make one invoice and print it and then need to make another one all you would need to do is press a key combination to update the invoice number.  This would only be useful on the one that updates on workbook open not on print.  It would prevent you from having to close the workbook and reopen it to update the invoice again.

Let me know something
0
 
AiricRAuthor Commented:
It doesnt even have to be Excel...
0
 
ccchinCommented:
I just can give u this guide at VBA, the rest u need to study yourself..

Example :=

Sub AutoNum()
Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = CStr(regValue)
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
End With

Good lucky to u
End Sub
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
krishmohCommented:
Open the excel sheet and go to  Tools --> Macro. Click on record new macro option.Enter a name for your macro and also the shortcut key(if need be). Then click on the cell of the invoice number. Now click on the stop recording macro menu option which opens up when you click on record new macro option. Now press Alt + F11. It would take you to Visual Basic editor screen.
In this screen select the WorkBook On Load option and enter the code provided by ccchin....

Const DEFAULTSTART As Integer = 1
Const MYAPPLICATION As String = "Excel"
Const MYSECTION As String = "myInvoice"
Const MYKEY As String = "myInvoiceKey"
Const MYLOCATION As String = "A1"
Dim regValue As Long

With ThisWorkbook.Sheets(1).Range(MYLOCATION)
If .Text <> "" Then Exit Sub
regValue = GetSetting(MYAPPLICATION, MYSECTION, _
MYKEY, DEFAULTSTART)
.Value = CStr(regValue)
SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
End With

This should help you in auto incrementing the invoice number.

0
 
ccchinCommented:
Ai ya..sorry..forgot to provided the step by step..Thank krishmoh..
0
 
AiricRAuthor Commented:

I went through all that and I could not find "WorkBook On Load option" Below is the invoice if you could doittoit !?


http://www.farrarconstruction.com/invoice.rar
0
 
AiricRAuthor Commented:
Oh well,,,,Thanks anyways guys...
0
 
jwleonard1Commented:
Don't give up yet!  I might be able to help you when I get to my home computer, the file you linked to is a rar and I don't have a program at work that can open it.  When I get home I will take a look at it.
0
 
AiricRAuthor Commented:
0
 
jwleonard1Commented:
I skipped an important part, when you press ALT+ F11 and the VB window opens you will need to double click "ThisWorkbook" in the VBA project window on the left side before pasting you code in the white area on the right.
0
 
AiricRAuthor Commented:
____ jwleonard1 ___


Eric@shreveporthandyman.com


0
All Courses

From novice to tech pro — start learning today.