Help with Auto-Generating unique ID in Excel VBA UserForm

CAmelb · Apr 6th 2018

Hi members.

I am a new member and a beginner at VBA coding and building UserForms. I would like to ask for some help from fellow members if possible please.

I have created a data entry userform. On this form is a textbox which I would like a unique new entry id to be generated each time I open and use the userform to enter new data.

For example, a unique id for the new entry would be: AAFL0001. The next entry would be: AAFL0002, and so on.

'AAFL' would be constant and '0001' would be sequential.

When I complete all the fields in my userform and add the entry to my spreadsheet then the unique id would also be added.

I would like to kindly ask for some guidance with the VBA coding for this please

chirayuw · Apr 6th 2018

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

Display More

CAmelb · Apr 6th 2018

Hi chirayuw,

Thank you for your reply and assistance with my request.

With the example code you kindly provided, will that generate an automatic unique ID in the Textbox ? (See below)

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"ExcelVBA UserForm_Capture01_Crop-wDwg.jpg","data-attachmentid":1202111}[/ATTACH]

Do I enter the code you provided in the Userform code here (See below)

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"ExcelVBA UserForm_Capture02_Crop-wDwg.jpg","data-attachmentid":1202112}[/ATTACH]

I am a beginner at VBA so I have limited knowledge of the coding practices.

Just to clarify, do you suggest changing Dim to Public and adding the code after changing to 'Private Sub UserForm_Initialize ()'

CAmelb · Apr 8th 2018

Hi chirayuw,

Thank you for the code you provided. It worked successfully.

Initially I couldn't get it to work due to my lack of knowledge in VBA, however it finally worked through trial and error.

Thank you again.

CA

Help with Auto-Generating unique ID in Excel VBA UserForm

Participate now!

Share