How to Auto-Generating unique ID in Excel VBA UserForm

Here's some example code. Assumes UID is in column A. To paste into sheet is simple as you'll just do something like Range("A" & LstRow +1) = NVal

Just FYI you might be better of changing those Variables to Public so the values can be used by your other buttons in the UserForm. So Dim changes to Public & the Variables will be outside the button code i.e Public.... then Userform_Initialize.

Code:

Dim LstRow As Long 'Figure out Last Value in Range
Dim OVal As String 'Old Value
Dim NVal As String 'New Value

With Sheet1
    If .Range("A2") = "" Then
        .Range("A2") = "AAFL0001"
    Else
        LstRow = .Cells(Rows.Count, "A").End(xlUp).Row
        OVal = .Range("A" & LstRow)
        NVal = "AAFL" & Format(Right(OVal, 4) + 1, "0000")
        Me.TextBox1.Value = NVal
    End If
End With

Obtained from the OzGrid Help Forum.

Solution provided by chirayuw.