Help with Auto-Generating unique ID in Excel VBA UserForm

  • 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

  • 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

  • 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 ()'

  • 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!