Do a google search on Auto Numbering. There are many
examples.
I found this one posted by Frank Kabel on April 5, 2004,
and another solution by Ross on the same thread.
Charlie O'Neill
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
--
Regards
Frank Kabel
Frankfurt, Germany
And also this one by 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
