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

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

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


"Susan" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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



--
HTH

Bob Phillips

"Susan" <[email protected]> wrote in message
news:[email protected]...
> 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

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

"GregR" <[email protected]> wrote in message
news:[email protected]...
> 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?

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

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.


--
HTH

Bob Phillips

"GregR" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" wrote:

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

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

Simple solution ...

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


--
bhawanePosted from http://www.pcreview.co.uk/ newsgroup access

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
'-------------------------------------------------------------



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> --
> HTH
>
> Bob Phillips
>
> "GregR" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

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

--
Dana DeLouis
Win XP & Office 2003


"GregR" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"Bob Phillips" wrote:

> 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
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "Susan" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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.

"bhawane" wrote:

>
> Simple solution ...
>
> Private Sub Workbook_Open()
> MyInv = Sheets("Sheet1").Range("A1").Value
> MyInv = MyInv +1
> Sheets("Sheet1").Range("A1") = MyInv
> End Sub
>
>
> --
> bhawanePosted from http://www.pcreview.co.uk/ newsgroup access
>
>

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.

--
HTH

Bob Phillips

"Susan" <[email protected]> wrote in message
news:[email protected]...
> I tried this and get and "invalid outside procedure" with the "UniqueID"
> hi-lited. what am I doing wrong. Susan
>
> "Bob Phillips" wrote:
>
> > 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
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Susan" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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!

"bhawane" wrote:

>
> Simple solution ...
>
> Private Sub Workbook_Open()
> MyInv = Sheets("Sheet1").Range("A1").Value
> MyInv = MyInv +1
> Sheets("Sheet1").Range("A1") = MyInv
> End Sub
>
>
> --
> bhawanePosted from http://www.pcreview.co.uk/ newsgroup access
>
>