Autonumerics in Excel from UserForm when clicking on Button

%3CLINGO-SUB%20id%3D%22lingo-sub-2825019%22%20slang%3D%22en-US%22%3EAutonumerics%20in%20Excel%20from%20UserForm%20when%20clicking%20on%20Button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825019%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20have%20a%20UserForm%20in%20VBA%20Excel%20to%20record%20data%20in%20an%20Excel%20table.%3CBR%20%2F%3EWhat%20I%20need%20is%20that%20each%20time%20the%20%22Register%22%20button%20of%20my%20Form%20is%20clicked%2C%20in%20the%20first%20cell%20of%20each%20row%20it%20will%20generate%20something%20like%20an%20Autonumeric%20with%20format%20%22REG-0001%22%20and%20when%20reloading%20data%20in%20the%20form%20and%20when%20pressing%20this%20button%20again%2C%20do%20so%20with%20the%20code%20%22REG-0002%22%20...%2003.04%20and%20so%20on%20....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20guide%20me%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2825019%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825044%22%20slang%3D%22en-US%22%3ERe%3A%20Autonumerics%20in%20Excel%20from%20UserForm%20when%20clicking%20on%20Button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825044%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179216%22%20target%3D%22_blank%22%3E%40DreamerMX%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20give%20this%20a%20try...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EPlace%20the%20following%20Function%20on%20the%20UserForm%20Module%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20getSequence(ws%20As%20Worksheet%2C%20lr%20As%20Long)%0ADim%20Matches%20As%20Object%0AWith%20CreateObject(%22VBScript.RegExp%22)%0A%20%20%20%20.Global%20%3D%20False%0A%20%20%20%20.Pattern%20%3D%20%22REG-(%5Cd%2B)%22%0A%20%20%20%20If%20.test(ws.Cells(lr%2C%201).Value)%20Then%0A%20%20%20%20%20%20%20%20Set%20Matches%20%3D%20.Execute(ws.Cells(lr%2C%201).Value)%0A%20%20%20%20%20%20%20%20getSequence%20%3D%20Matches(0).submatches(0)%0A%20%20%20%20%20%20%20%20getSequence%20%3D%20getSequence%20%2B%201%0A%20%20%20%20%20%20%20%20getSequence%20%3D%20%22REG-%22%20%26amp%3B%20Format(getSequence%2C%20%22000%22)%0A%20%20%20%20End%20If%0AEnd%20With%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3EThen%20add%20the%20following%20lines%20in%20the%20existing%20code%20underneath%20the%20Submit%20Button%20on%20UserForm%20Module%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDim%20ws%20As%20Worksheet%0ADim%20lr%20As%20Long%0ASet%20ws%20%3D%20ActiveSheet%20%20%20%20'Change%20the%20Sheet%20if%20required%0Alr%20%3D%20ws.Cells(Rows.Count%2C%201).End(xlUp).Row%0AIf%20lr%20%3D%201%20Then%0A%20%20%20%20ws.Cells(lr%20%2B%201%2C%201).Value%20%3D%20%22REG-001%22%0AElse%0A%20%20%20%20ws.Cells(lr%20%2B%201%2C%201).Value%20%3D%20getSequence(ws%2C%20lr)%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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

@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