ajm
01-31-2010, 08:07 PM
in an earlier post (http://www.vbaexpress.com/forum/showthread.php?t=30241), mdmckillop and geekgirlau assisted me with an autonumbering macro :cloud9:.
i want to be a bit cheeky and base the final format of the autonumber on the start date of the project it identifies. that is, if the start date is in 2010, the autonumber will be "GP10-009" or if its a 2011 start, then the number would appear as "GP11-006".
two things, how do i incorporate the year number into the autonumber, and secondly, can it be set up that if there are a swag of 2010 projects and then a 2011 project gets set up, that 2011 project should be the next incrementing 2011 project number and not just the next 2010 number with GP11 in front.
so from above, if we were up to GP10-009 and a 2011 project was entered, the number should be GP11-001 (not GP11-010).
code from mdmckillop and geekgirlau is:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'///add a project number when cell within range(s) is doubleclicked
Dim c As Range
Dim d As Range
Dim y As Integer
'///locate cell for project number
Set c = Intersect(Target, Range("RefNbrRg"))
If c Is Nothing Then Exit Sub
If c <> "" Then Exit Sub
Cancel = True 'Prevent going into Edit Mode
'///grab year number at left of c
Set d = c.Offset(0, -1)
y = Right(d.Value, 2)
' number has not already been assigned
If Cells(c.Row, 9).Value = 0 Then
' check that all required values have been entered
If Application.CountA(Cells(c.Row, 1).Resize(, 8)) = 8 Then
' get the nextnumber
Cells(c.Row, 9) = WorksheetFunction.Max(Range("RefNbrRg")) + 1
Cells(c.Row, 9).NumberFormat = """GP10-""000"
Else
MsgBox "Please complete ALL details of project"
End If
End If
End Sub
i want to be a bit cheeky and base the final format of the autonumber on the start date of the project it identifies. that is, if the start date is in 2010, the autonumber will be "GP10-009" or if its a 2011 start, then the number would appear as "GP11-006".
two things, how do i incorporate the year number into the autonumber, and secondly, can it be set up that if there are a swag of 2010 projects and then a 2011 project gets set up, that 2011 project should be the next incrementing 2011 project number and not just the next 2010 number with GP11 in front.
so from above, if we were up to GP10-009 and a 2011 project was entered, the number should be GP11-001 (not GP11-010).
code from mdmckillop and geekgirlau is:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'///add a project number when cell within range(s) is doubleclicked
Dim c As Range
Dim d As Range
Dim y As Integer
'///locate cell for project number
Set c = Intersect(Target, Range("RefNbrRg"))
If c Is Nothing Then Exit Sub
If c <> "" Then Exit Sub
Cancel = True 'Prevent going into Edit Mode
'///grab year number at left of c
Set d = c.Offset(0, -1)
y = Right(d.Value, 2)
' number has not already been assigned
If Cells(c.Row, 9).Value = 0 Then
' check that all required values have been entered
If Application.CountA(Cells(c.Row, 1).Resize(, 8)) = 8 Then
' get the nextnumber
Cells(c.Row, 9) = WorksheetFunction.Max(Range("RefNbrRg")) + 1
Cells(c.Row, 9).NumberFormat = """GP10-""000"
Else
MsgBox "Please complete ALL details of project"
End If
End If
End Sub