Autonumerics in Excel from UserForm when clicking on Button

Question

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 ....&nbsp;Can someone guide me? Thanks!

subodh_tiwari_sktneer · Accepted Answer

DreamerMX&nbsp;&nbsp;Please give this a try...&nbsp;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-" &amp; Format(getSequence, "000")
    End If
End With
End Function&nbsp;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

Forum Discussion

Autonumerics in Excel from UserForm when clicking on Button

1 Reply