Autonumerics in Excel from UserForm when clicking on Button

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