Creating a sequentual serial no within a template

[Guest]

I am attempting to Create a sequentual serial no within a template ( for
invoices / quotes) within a particular cell in the template. So when I open a
new workbook from the template it increments the invoice serial number by
one. Pulling my hair out with this one.

 

[Nick Hodge]

Stix

There are lots of ways to do this, all of which use event code (IMO). You
can trigger events based on many things, Workbook_Open,
Workbook_BeforePrint, Workbook_BeforeSave, Workbook_Close, etc. So you need
to decide what triggers a new number (You could also have a cell you click
in that does it too). Once you have selected the trigger the code would look
like

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Range("B1").Value = Range("B1").Value + 1
End If
End Sub

this works on the worksheet_selectionchange() event, so if you select A1 on
the sheet, B1 will increment by one, otherwise nothing happens. To implement
check here.

http://www.nickhodge.co.uk/vba/vbaimplement.htm#EventCode

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[JE McGimpsey]

For one way, see

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html