Using a text file to hold sequential numbers
This method is more useful in some situations. The biggest advantage is that the sequential number is no longer tied to a particular machine - it can be stored on a common server, or even a thumb drive. Disadvantages include difficulty in keeping the file from being modified simultaneously by two users, or of the file being more easily deleted or modified. This function will return the next sequential number:
Public Function GetNextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
Const sDEFAULT_PATH As String = "<your path here>"
Const sDEFAULT_FNAME As String = "defaultseq.txt"
Dim nFileNumber As Long
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
nSeqNumber = nSeqNumber + 1
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
Close nFileNumber
NextSeqNumber = nSeqNumber
Exit Function
PathError:
NextSeqNumber = -1
End Function
If you provide a full path in sFileName, that's where the file will be stored. If not, the file will be stored in whatever default directory you specify. You can set the sequential number by providing a value for nSeqNumber.
Thus, if I'm only using one sequence I can use
Public Sub Workbook_Open()
ThisWorkbook.Sheets(1).Range("B2").Value = GetNextSeqNumber
End Sub
to return the next sequence number. If I'm using multiple sequences, I include the filename (with path, if the text file is not in the default path).
Public Sub NewClientInvoice()
This.Workbook.Sheets(1).Range("B2").Value = GetNextSeqNumber("Client1.txt")
End Sub
And if I want to start a new sequence, beginning at, say, 1001, include that number in the function call. If the client name were in cell B4:
Public Sub SetUpNewClient()
With ThisWorkbook.Sheets(1)
.Range("B2").Value = GetNextSeqNumber(.Range("B4").Value & ".txt", 1001)
End With
End Sub
Original article credit to:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.htmlExcel is a licensed product of Microsoft Corporation