Creating a sequentual serial no within a template

  • Thread starter Thread starter Guest
  • Start date Start date
G

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