ID number generation - with prefix

Hi everyone,

I am currently working on a personal project, creating a macro in excel to better manage a register of businesses.
It is incomplete at the moment.

Currently, I have a userform with a field licencetext where the user can enter a number which acts as an ID number (the prefix "FB" is added during transfer of data from the userform to the sheet.

However, if the field is left blank, a would like excel to generate the next unique three digit number available (and verify that is not a duplicate)

Any ideas on how to do this? I have had a look at some other threads on the net, but can't seem to find something relevant or easy to understand - I am new to VBA and entirely self-taught mainly through just google and looking at examples... yea I know, it's pretty bad form XD


Edit. Attachment included - please note the code and layout of userform are not complete

generate the next unique three digit number available

assume you have a list of sorts....so take the last number and plus 1?

Post an example file with desensitized data if possible.

Try this.

Private Sub SubmitButton_Click()

'Validating that the user is sure they woud like to submit

Dim Answer As Integer

Answer = MsgBox("Are you sure you are ready submit the data?", vbYesNo + vbQuestion, "Submit New Entry")

'Transfer data into the worksheet

Dim emptyRow As Long

Sheet1.Activate

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 5

If Answer = vbYes Then

    Cells(emptyRow, 1).Value = TradingNameText.Value
    If licencetext.Value = True Then Cells(emptyRow, 2).Value = "FB" & licencetext.Value
    If licencetext.Value = False Then
        For Each cl In Range("B6", Range("B" & Rows.Count).End(xlUp))
            If cl.Value <> vbNullString Then
                mymax = Application.Max(mymax, ExtractNumber2(cl.Value))
            End If
        Next
        Cells(emptyRow, 2).Value = "FB" & mymax + 1
    End If
Else
'do nothing
End If

Unload Me

End Sub

Function ExtractNumber2(rC As String) As Long
    With CreateObject("VBSCRIPT.REGEXP")
        .Pattern = "[^0-9]"
        .Global = True
        .IgnoreCase = True
        ExtractNumber2 = .Replace(rC, "")
    End With
End Function

so you have FB123, FB456, FB431

what would you expect to see as the next number?
FB124? then 125 126 etc
FB432 then 433 434 etc
FB456 then 457 458
or something else entirely?

That's not much use, really. In what way did it not seem to work? Outcome? Error messages? What???

There was no error when running macro, but regardless no new number generated and input into the worksheet.

Your wish is my command.

Oh yes, it works.
Run this in your example file.

Sub NewEntry()
        For Each cl In Range("B6", Range("B" & Rows.Count).End(xlUp))
            If cl.Value <> vbNullString Then
                mymax = Application.Max(mymax, ExtractNumber2(cl.Value))
            End If
        Next
        MsgBox mymax + 1
End Sub

Function ExtractNumber2(rC As String) As Long
    With CreateObject("VBSCRIPT.REGEXP")
        .Pattern = "[^0-9]"
        .Global = True
        .IgnoreCase = True
        ExtractNumber2 = .Replace(rC, "")
    End With
End Function

Can attach a worksheet where this works? I cannot seem to work out where it is going wrong on mine, the Msgbox does not appear. Perhaps I am putting it in the wrong place?

Glad to help and thanks for rep+.