Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sequential Numbering in a Template
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sequential Numbering in a Template
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 "Silena K-K" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sequential Numbering in a Template
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 On Tue, 1 Apr 2008 11:35:00 -0700, Silena K-K wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sequential numbering | Excel Discussion (Misc queries) | |||
PO with sequential numbering with start / end numbering | Excel Discussion (Misc queries) | |||
Sequential Numbering | Excel Discussion (Misc queries) | |||
Sequential numbering | Excel Worksheet Functions | |||
Sequential Numbering. | New Users to Excel |