Autonumerics in Excel from UserForm when clicking on Button

[DreamerMX]

Hi everyone!

I have a UserForm in VBA Excel to record data in an Excel table.
What I need is that each time the "Register" button of my Form is clicked, in the first cell of each row it will generate something like an Autonumeric with format "REG-0001" and when reloading data in the form and when pressing this button again, do so with the code "REG-0002" ... 03.04 and so on ....

Can someone guide me? Thanks!

[#2 · Accepted solution]

@DreamerMX 

Please give this a try...

Place the following Function on the UserForm Module:

Function getSequence(ws As Worksheet, lr As Long)
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "REG-(\d+)"
    If .test(ws.Cells(lr, 1).Value) Then
        Set Matches = .Execute(ws.Cells(lr, 1).Value)
        getSequence = Matches(0).submatches(0)
        getSequence = getSequence + 1
        getSequence = "REG-" & Format(getSequence, "000")
    End If
End With
End Function

Then add the following lines in the existing code underneath the Submit Button on UserForm Module:

Dim ws As Worksheet
Dim lr As Long
Set ws = ActiveSheet    'Change the Sheet if required
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
If lr = 1 Then
    ws.Cells(lr + 1, 1).Value = "REG-001"
Else
    ws.Cells(lr + 1, 1).Value = getSequence(ws, lr)
End If