?
Solved

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

Posted on 2005-04-03
11
Medium Priority
?
14,436 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:AiricR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 1

Author Comment

by:AiricR
ID: 13695397
It doesnt even have to be Excel...
0
 
LVL 3

Expert Comment

by:ccchin
ID: 13695980
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
 

Expert Comment

by:krishmoh
ID: 13696290
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 3

Expert Comment

by:ccchin
ID: 13696336
Ai ya..sorry..forgot to provided the step by step..Thank krishmoh..
0
 
LVL 1

Author Comment

by:AiricR
ID: 13699562

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
 
LVL 1

Author Comment

by:AiricR
ID: 13703114
Oh well,,,,Thanks anyways guys...
0
 
LVL 3

Expert Comment

by:jwleonard1
ID: 13712003
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
 
LVL 1

Author Comment

by:AiricR
ID: 13712067
0
 
LVL 3

Accepted Solution

by:
jwleonard1 earned 500 total points
ID: 13713230
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
 
LVL 3

Expert Comment

by:jwleonard1
ID: 13713253
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
 
LVL 1

Author Comment

by:AiricR
ID: 13713294
____ jwleonard1 ___


Eric@shreveporthandyman.com


0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
This article was originally published on Monitis Blog, you can check it here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question