How do you add 1 to an invoice number in Excel. Formula for this?

[Susan]

I'm trying to set up my own invoices. What I can't seem to do is create a
cell for my invoice number, that every time I open the file it adds 1 (0ne)
to the invoice number. Does anyone have a macro - formula for doing this?
Thank you

 

[Anne Troy]

This wil probably help you a lot, Susan:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=348

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com

 

[DM Unseen]

Use the following code:


'proc for generating invoice numbers form excel. Requires a standard
excel book, not a template, that cannot be set to a shared workbook.
Also it needs a custom documentproperty "template" of type yesno.
'This setup will work with multiple users given the basic xl file is
accesible to all users.
Users should never open the original template as read only.

' procedure voor het automatisch genereren van een invoice nummer
vanuit excel
' Uitgangspunt is een gewoon excel bestand dat niet als template
gebruikt wordt en ook
' niet gedeeltd wordt. Anders kan
' het laatst gebruikte factuurnummer niet teruggeschreven worden naar
de "Template"
' en wordt de factuurtemplate niet beschermd tegen openen door meerdere
gebruikers
' Er moet een documenteigenschap "Template" aangemaakt worden die
aangeeft of het document een
' template is of een factuur.

Private Sub Workbook_Open()
Dim lngInvoiceNr As Long
Dim strName As String
Dim intPos As Integer


' Als bestand een template is, meteen foutmelding en sluiten
If Me.Path = "" Then
MsgBox "factuur geopend als template, gaarne openen als gewoon
bestand", vbCritical, "factuur"
Me.Saved = True
Me.Close
GoTo Exit_here
End If

On Error GoTo Error1

' als dit de template is dat een factuur creeren
If Me.CustomDocumentProperties("Template") Then

'factuurnummer ophogen
With Me.Worksheets(1).Range("M3")
.Value = .Value + 1
lngInvoiceNr = .Value
End With


' template met nieuw factuurnummer opslaan
Application.DisplayAlerts = False
Me.Save
' opgeslagen, dus dit bestand is nou geen template meer
Me.CustomDocumentProperties("Template") = False
Application.DisplayAlerts = True

' bestandsnaam factuurnummer aanmaken
strName = Me.Name
intPos = InStrRev(strName, ".")
If intPos > 0 Then strName = Left$(strName, intPos - 1)

' aangeven dat factuur nog niet is opgeslagen
Me.Saved = False

' gebruiker vragen om factuur op te slaan
While Not Me.Saved
Application.Dialogs(xlDialogSaveAs).Show strName &
CStr(lngInvoiceNr)
Wend



End If
Exit_here:
' exit code kan hier

Exit Sub
Error1:

MsgBox Err.Description
GoTo Exit_here
End Sub


DM Unseen

 

[Bob Phillips]

You could define a name in your workbook, and use this value when the file
opens.

Add this code to theThisWorkbook code module within the workbook and it will
automatically increment the Name UniqueId every time the workbook is opened.

You can then acess that name in your code by plugging this into the existing
code that needs the Id.

Evaluate(ThisWorkbook).Names("__UniqueId").RefersTo)

'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
GetId
End Sub

'-------------------------------------------------------------
Private Sub GetId()
'-------------------------------------------------------------
Dim myId As Long

myId = 1 ' in case it doesn't already exist
On Error Resume Next
myId = Evaluate(ThisWorkbook.Names("__UniqueId").RefersTo) + 1
ThisWorkbook.Names.Add Name:="__UniqueId", RefersTo:="=" & myId

End Sub
'-------------------------------------------------------------

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[GregR]

Bob, this is what I have in the ThisWorkbook code module:

Evaluate(ThisWorkbook).Names("UniqueId").RefersTo ("Inv")
Private Sub Workbook_Open()
GetId
End Sub
Private Sub GetId()

Dim myId As Long

myId = 1 ' in case it doesn't already exist
On Error Resume Next
myId = Evaluate(ThisWorkbook.Names("UniqueId").RefersTo) + 1
ThisWorkbook.Names.Add Name:="UniqueId", RefersTo:="=" & myId

End Sub

I have named "F4" with "Inv". I am sure I missed something, but not
sure what? TIA

Greg

 

[Bob Phillips]

Bob, this is what I have in the ThisWorkbook code module:

Evaluate(ThisWorkbook).Names("UniqueId").RefersTo ("Inv")
Private Sub Workbook_Open()
GetId
End Sub
Private Sub GetId()

Dim myId As Long

myId = 1 ' in case it doesn't already exist
On Error Resume Next
myId = Evaluate(ThisWorkbook.Names("UniqueId").RefersTo) + 1
ThisWorkbook.Names.Add Name:="UniqueId", RefersTo:="=" & myId

End Sub

I have named "F4" with "Inv". I am sure I missed something, but not
sure what? TIA

What does the last sentence mean exactly, and what is/is not happening?

 

[GregR]

Bob, what it means is I have defined "F4" by the user name "Inv". I
thought that's what you meant by "define a name in your workbook". HTH

Greg

 

[Bob Phillips]

Talk about crossed wires, I thought you meant the F4 function key <vbg>

No, when I said define a name, I meant doing it in code, and that is what is
happening in this lien

ThisWorkbook.Names.Add Name:="__UniqueId", RefersTo:="=" & myId

So you need to use that name __UniqueId.

Oh, and it doesn't need to be tied to a cell, it is a name with just a
value.

I'll re-write it tomorrow (bed-time) and try and explain it better.

 

[Susan]

Hi, I'm sure this works. But can't figure out exactly what you are saying as
some of this seems to be in German. I need a simple step by step explanation
as I'm new to Excel. This was simple for me to do in Lotus. In lotus you name
the macro "0" which means it runs everytime you open the workbook and then
the formula/macro is "get the cell " +1, and then "Save the file" and then
"Quit" and it all works fine. Is there a simple way to do this is Excel with
out writing a novel?

 

[DM Unseen]

Susan,

it's Dutch, not German (go, cloggies go. I made this code for a
dutch excel newsgroup poster.

What you want needs VBA. VBA is Excel's macro language, although it is
in fact a full programming language. (Excel has another macro language,
one looking more like lotus, called XLM, but official support has
dropped from version 5 onwards) . If you want to be as good on Excel as
on Lotus, there is no way around VBA!

My code works more or less the same as Bob's & Greg's.
Just replace the reference "M3" from the code from:
**With Me.Worksheets(1).Range("M3") **
with the correct cell with the invoice number. You could also use
define->name and use that name instead.
Also do not forget to add the custom document property.

The *difference* lies in the invoice file management. the dutch poster
requested that once the invoice number on the file has been created you
want to save the file under another name for later use, and not have
the number increase *again* when you later open that invoice again.
This means that each invoice can have it's own workbook, but still all
workbooks should contain a unique number! As far as I understand Bob's
code will update the invoice number *each time* you open a file which
has that code in it. So there is just one workbook that each time you
open it becomes a new invoice(since the numer is increased). This is
more like your request, so you pick your solution and just request
additional help with installing the VBA on your file.

DM Unseen

 

[bhawane]

Simple solution ...

Private Sub Workbook_Open()
MyInv = Sheets("Sheet1").Range("A1").Value
MyInv = MyInv +1
Sheets("Sheet1").Range("A1") = MyInv
End Su

 

[Bob Phillips]

Greg,

Here is my re-written text. Can you give me feedback if this explains it
better.

Thanks

Bob


Here is a technique to add an incrementing id to a workbook, one which
increments each time that the workbook is opened. This can be very useful
for maintaining invoice numbers, tracking changes etc.

In essence, this technique uses an Excel name, which doesn't refer to a
range, but to a number. You could define this name yourself in your
workbook, and add code to increment it, but this technique does all of that
work.

Once this code is installed, the incrementing id can be accessed as follows

In a worksheet

=__UniqueId

In VBA

Evaluate(ThisWorkbook.Names("__UniqueId").RefersTo)

Not that the name of the Excel name is defined as a constant within the
code. I use __UniqueId, but it can easily be changed to whatever you want to
use.

Implementation

This is workbook event code, so it needs to be added the to the ThisWorkbook
code module within the workbook.

To input this code, right click on the Excel icon on the worksheet (or next
to the File menu if you maximise your workbooks), select View Code from the
menu, and paste the code in the code pane that pops up.

Private Const sIdName As String = "__UniqueId"

'-------------------------------------------------------------
Private Sub Workbook_Open()
'-------------------------------------------------------------
GetId
End Sub

'-------------------------------------------------------------
Private Sub GetId()
'-------------------------------------------------------------
Dim myId As Long

myId = 1 ' in case it doesn't already exist
On Error Resume Next
myId = Evaluate(ThisWorkbook.Names(sIdName).RefersTo) + 1
ThisWorkbook.Names.Add Name:=sIdName, RefersTo:="=" & myId

End Sub
'-------------------------------------------------------------

 

[Dana DeLouis]

Hi. This is similar to Bob's excellent idea. Would anything here work for
you?

Private Sub Workbook_Open()
On Error Resume Next
ThisWorkbook.Names.Add "InvoiceNumber", [InvoiceNumber] + 1
If Err.Number > 0 Then ThisWorkbook.Names.Add "InvoiceNumber", 1

'// You can put the current value of Invoice in a cell...
[A1] = [InvoiceNumber]
'or ...
Range("A2") = ThisWorkbook.Names("InvoiceNumber").Value
End Sub

 

[Susan]

I tried this and get and "invalid outside procedure" with the "UniqueID"
hi-lited. what am I doing wrong. Susan

 

[Susan]

Works great, now how do i write "save file" at the end. That way I'll use
this as a template and each time I use it it will add 1 to the invoice
number. Sorry I'm so inept. I'm just learning.

 

[Bob Phillips]

I presume that you mean the

Evaluate(ThisWorkbook).Names("__UniqueId").RefersTo)

bit.

It was just a line to show you how to get the value, it has to be included
in a macro of yours.

 

[Susan]

This works perfect, add a (me.save) on the end and it makes a perfect
template for all my invoices. Thank you so much. It's hard to do this coming
from Lotus, but I'm slowly getting the hang of it. Thanks again!