Sequential Numbering in a Template

[Silena K-K]

Hi there

I have used the sequential numbering code on the McGimpsey site for a
template I have designed in Excel to insert the next consecutive number each
time the template is opened into a new workbook.

However now what happens is when I save the workbook and reopen it, it
automatically inserts the next sequential number.

How do I restrict the code to inserting a new number into a new workbook
when the template is used but not re-inserting a new number every time the
saved workbook is re-opened.

Thank you, Silena

 

[Mike H]

Read JE McGimpseys code properly and you will see it uses IsEmpty and if
that evaluates as false nothing happens. Apologies to JE McGimpsey for
posting his code
without permission

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Invoice")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

Mike

 

[Gord Dibben]

Dave Peterson posted this a while back.

If the workbook has never been saved, which is the case with a newly created
workbook from the Template, the invoice number will increment.

After saving, the number will not increment when opening that saved workbook.

Private Sub Workbook_Open()
if thisworkbook.path = "" then
'it's never been saved, so increment
Sheet1.Range("H16").Value = Sheet1.Range("H16").Value + 1
end if
End Sub


Gord Dibben MS Excel MVP