EXCEL SEQUENTIAL NUMBERING ON FORMS

For this task you need to know some basic programming knowledge. I can share a code to get new numbers from a database however before using this code in your macro there are several steps you need to know and apply:

1. You need to have a workbook acting as a database where you have all the numbers stored

2. You need to open this database workbook before you run this macro whether manually or by macro itself

3. The first sheet of the database workbook is the sheet where all the numbers and relative data is stored

4. there must be a row of headings in this sheet.

5. this code only gets a new number and the last empty row and saves them in MewRecordNumber and RecordRow as public variable which you can use in other modules in the same macro workbook. So it is up to you to write additional code to save this information to your database.

Option Explicit
Public NewRecordNumber As String 'the new record number that we generate
Public RecordRow As Double 'to store where we need to add the new number on database
Private DefTxt As String 'this is the default string that is always in the beginning of the number
'
Function GetNewNumber() As Boolean


'record number consists of a default text, year and 4 digit number that starts from 0 every new year
'example: DefStr-18-0001
Dim Database As Workbook
Set Database = Workbooks("Database.xlsx")

Dim DBSheet As Worksheet
Set DBSheet = Database.Worksheets(1)

DefTxt = "DftStr"

RecordRow = DBSheet.Cells(DBSheet.Rows.Count, "A").End(xlUp).Row

'this is to search if there are any default text in the number we create
'if there are no defalut text in the number you can ommit these lines
If InStr(1, DBSheet.Cells(RecordRow, 1).Value, DefTxt, vbTextCompare) = 0 Then

CreateInitialNumber
GetNewNumber = True
Debug.Print NewRecordNumber
Exit Function
End If
'end of searching defalut text

Dim LastRecordNumber

'splitting the last record number in to an array by - sign
'I will have an array of 3 values which:
' LastRecordNumber(0)=Default text
' LastRecordNumber(1)=Year
' LastRecordNumber(2)=4 digit number
LastRecordNumber = DBSheet.Cells(RecordRow, 1).Value
LastRecordNumber = Split(LastRecordNumber, "-")

'I am checking the year of last record number and if it is different than current year
'I will create a new number
If CInt(LastRecordNumber(1)) <> Right(CStr(Year(Date)), 2) Then
CreateInitialNumber
GetNewNumber = True
Debug.Print NewRecordNumber
Exit Function
Else

CreateNewNumber (LastRecordNumber)

End If

Debug.Print NewRecordNumber
GetNewNumber = True

End Function

Private Sub CreateInitialNumber()
NewRecordNumber = DefTxt & "-" & Right(CStr(Year(Date)), 2) & "-0001"
RecordRow = RecordRow + 1
End Sub

Private Sub CreateNewNumber(LastRecordNumber As Variant)

Dim No As String
'lastrecordnumber(2) has the last number that is in the database
No = CStr(CDbl(LastRecordNumber(2)) + 1)

'if the number is 1 digit I add 0's to have a four digit number as string
If Len(No) < 4 Then
Dim Say As Integer
For Say = Len(No) To 3
No = "0" & No
Next Say
End If
NewRecordNumber = DefTxt & "-" & LastRecordNumber(1) & "-" & No
RecordRow = RecordRow + 1

End Sub