Auto Assign Serial No.'s

  • Thread starter Thread starter TedN
  • Start date Start date
T

TedN

How can I have Excel automatically assign a serial number
for a form, i.e., P.O., Invoice, etc.?

Ideally, I would like to have a table populatd with a
list of numbers and each time a new form is created using
the respective template, have the number populate the
designated field and eliminated from the lookup table.
 
Hi
not really that simple. Below find a solution which autoincrements a
value and stores the old value in your registry. you could change the
format of this serial number:

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