SOLVED

Autonumerics in Excel from UserForm when clicking on Button

New Contributor

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!

1 Reply
best response confirmed by DreamerMX (New Contributor)
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