Excel vba increment unique id for each row

[Jaye Cavallo]

Hello,

I am seeking an Excel VBA method to create a unique ID for every row of data that is entered by an attendee to an event. I have the userform designed and the data from the form is passed on to the worksheet as expected. Is there a way to create a unique id every time an attendee enter his information and the data is saved to the work sheet?

 

[Akuini]

1. Can you provide some examples of the IDs?
2. Do they need to have sequential numbers, such as A001, A002, etc.?

 

[Jaye Cavallo]

Yes, in sequential order is what I was hoping for, like B10001, B10002, B10003, etc.

 

[Akuini]

and the data is saved to the work sheet?

Can you show us the code which send data to the worksheet?
And the ID's are in col A?

 

[Jaye Cavallo]

Can you show us the code which send data to the worksheet?
And the ID's are in col A?

Sorry, here it is the code that writes to the worksheet. The ID's don't have to be column A, but if you tell me there is good reason why the ID's should be in column A, then I can adjust my code. Currently, the ID's would be in column L, I just don't have that code written yet.

Private Sub cmdSave_Click()

'Validation
If Me.TextBox1.Value = "" Then
MsgBox "Please enter your first name", vbCritical
Me.TextBox1.SetFocus
Exit Sub
End If

If Me.TextBox2.Value = "" Then
MsgBox "Please enter your last name", vbCritical
Me.TextBox2.SetFocus
Exit Sub
End If

'Validation.
If Me.TextBox3.Value = "" Then
MsgBox "Please enter a valid e-mail address", vbCritical
Me.TextBox3.SetFocus
Exit Sub
End If

Dim emptyRow As Long

'Make Sheet1 active
Worksheets("Registration").Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 4).Value = TextBox4.Value
Cells(emptyRow, 5).Value = TextBox5.Value
Cells(emptyRow, 6).Value = TextBox6.Value
Cells(emptyRow, 7).Value = TextBox7.Value
Cells(emptyRow, 8).Value = TextBox8.Value
Cells(emptyRow, 9).Value = TextBox9.Value
Cells(emptyRow, 10).Value = TextBox10.Value


End Sub

 

[Akuini]

Is it necessary for the ID to contain letters?
It would be more convenient if the ID consists only of numbers, such as 6 digits starting at 100001. This way, it will be simpler to identify the highest number in the data (column L) using the MAX function. The new ID would be the highest number + 1.

 

[Jaye Cavallo]

It would not be required to have the ID start with a letter. I was using the letter to distinguish between multiple events and attendees in different counties. If I could begin the numbering for one county in 100001 and the second county with 200001 and the third county with 3000001, that would be great.

 

[Akuini]

I was using the letter to distinguish between multiple events and attendees in different counties.

What categories do you want the ID's to refer to? events,attendees or counties?

 

[Jaye Cavallo]

Attendees should have an ID

 

[Akuini]

Attendees should have an ID

What I mean was, what information/category do you want the ID to reflect?
For example: say the ID is like this: BF001
What is B, the first letter, means? it could mean a city where B means New York.
What is F, the second letter, means? it could mean gender where F means female.
Therefore, you need to construct the ID pattern based on the specific information you want to include.