Sequential Numbers

A

abcdexcel

Hi

I have got the code for sequential numbers from McGimpsey's website. Is
there a code, that the numbers restart on every change in month.

Thanks
SAS
 
B

Bob Phillips

You could add some code to test whether the date is the 1st and reset to
one, but is it possible that the workbook is opened more than once in a day?

BTW, which version are u using, registry or text file?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

Try this

Public Function NextSeqNumber(Optional sFileName As String, _
Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "C:\"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
Dim nFileNumber As Long
Dim nDate As String

nFileNumber = FreeFile
If sFileName = "" Then sFileName = sDEFAULT_FNAME
If InStr(sFileName, Application.PathSeparator) = 0 Then _
sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
If nSeqNumber = -1& Then
If Dir(sFileName) <> "" Then
Open sFileName For Input As nFileNumber
Input #nFileNumber, nSeqNumber, nDate
If Month(CDate(nDate)) <> Month(Date) Then
nSeqNumber = 1&
Else
nSeqNumber = nSeqNumber + 1&
End If
Close nFileNumber
Else
nSeqNumber = 1&
End If
End If
On Error GoTo PathError
Open sFileName For Output As nFileNumber
On Error GoTo 0
Print #nFileNumber, nSeqNumber, Format(Date, "dd mmm yyyy")
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1&
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top