Auto invoice numbering

[unkown]

I need a auto invoice numbering on a new worksheet that can add a
consequence invoice number from the previous worksheet number.

Any help is greatly appreciated.

 

[Frank Kabel]

Hi
do you really mean 'worksheet' and not 'workbook'. For the latter one
you may have a look at the following repost. It assumes you have a
invoice template (*.xlt file)

----------------
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)

Private Sub Workbook_Open()
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
End Sub

 

[Guest]

Using the registry means that the workbook can be used only on the PC whose registry holds the invoice number counter.

 

[ross]

Not very elegant, but easy to set up:

* what you need!
1 excel WB called "Invoice.xls" in which there are 2 sheets, "Invoice"
and "Data"

Concept:
You store a number in the data page which is updated when somthing
happens:

code bit one:
sheets("data").cells(1,1).value = sheets("data").cells(1,1).value +1

code bit 2
sheets("Invoice").Range("a2").value = sheets("data").cells(1,1).value

so this puts the number in a2 on the invoice sheet, which you format
as you like;

now where do you put these code?

well you can put code 2 in the open workbook event, and the code one
bit in the
before close, and you get a book that opens with a new invoice number
and updates it on each open/close,
Better, to put the 2 codes buttons on the Invocie sheet, and then you
can make as many invoice as you like, with out opening closing

so you get this

Private Sub CommandButton1_Click()
Sheets("data").Cells(1, 1).Value = Sheets("data").Cells(1, 1).Value +
1
Sheets("Invoice").Range("a2").Value = Sheets("data").Cells(1, 1).Value
End Sub

ross