Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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_he ' exit code kan hier Exit Sub Error1: MsgBox Err.Description GoTo Exit_here End Sub DM Unseen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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").RefersT o) '------------------------------------------------------------- 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
"GregR" wrote in message ps.com... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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" wrote in message oups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for th
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_he ' exit code kan hier Exit Sub Error1: MsgBox Err.Description GoTo Exit_here End Sub DM Unseen |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for th
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
Simple solution ... Private Sub Workbook_Open() MyInv = Sheets("Sheet1").Range("A1").Value MyInv = MyInv +1 Sheets("Sheet1").Range("A1") = MyInv End Su -- bhawanePosted from http://www.pcreview.co.uk/ newsgroup acces |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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" wrote in message ... 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" wrote in message oups.com... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for this?
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" wrote in message oups.com... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for th
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").RefersT o) '------------------------------------------------------------- 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" wrote in message ... 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for th
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you add 1 to an invoice number in Excel. Formula for th
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula for purchased items with same invoice number | Excel Discussion (Misc queries) | |||
invoice toolbar for invoice calcuation and assign number | Excel Discussion (Misc queries) | |||
How do I assign an invoice number using the invoice toolbar? | Excel Worksheet Functions | |||
How do I generate a new invoice number when creating new invoice? | Excel Discussion (Misc queries) | |||
How do I change the invoice number assigned in Invoice template... | Excel Discussion (Misc queries) |