vba help needed: to create unique ID for each row in Excel Worksheet

[Elena Margulis]

Hi,
I have data entry Worksheet (Survey), plz see below, where I need to generate unique identifier for each record (each Survey):

I have hidden column - SurveyCode, where the ID could be stored.
I was thinking about different ways of doing this:
If create formula like Row() - populating new number on each record, then in case if user would delete a row, the SurveyCode would change for the next / previous records.
Same problem if I'd use DEC2HEX(RANDBETWEEN(0, 4294967295), 8), even worse, because it changes any time on any action(s)...

I will have unknown numbers of records in this form (could be many thousands), that's why hard-coding limited amounts of records won't work either, and also creates problems when data entry occurs.
Formulas placed into SurveyCode could be destroyed in case if user will accidentally press tab button (which will create an empty row in this Form) and so force...

So, I am really looking for vba code that would create a unique id - for the hidden SurveyCode field - for each record, in this data entry Worksheet.
After deleting/ adding record(s) - the ID for all (already entered) records must not be changed or deleted!

Ideally, my SurveyCode should have the following format FileName-yyyy-mm-uniqueID (for example CSS-2020-08-345677, where CSS is FileName (or any text), 2020-08 is current yr/mo, 345677 is unique id )

Please help!
Thank you

 

[Peter_SSs]

Would you still want "-yyyy-mm-" inserted between the text and the unique number?

 

[Elena Margulis]

Answering your question - whichever is easier to update (less work)!

 

[Peter_SSs]

Try this version. Edit the 'Const' line near the top of the code. I have changed other parts of the code too so copy the whole lot.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim IDMaxSuffix As Long, i As Long
  Dim a As Variant
  
  Const IDPrefix As String = "ABC-2020-08-"   '<- Edit as you want
  
  If Not Intersect(Target, Range("Table1")) Is Nothing Then
    With Range("Table1[SurveyCode]")
      a = .Value
      For i = 1 To UBound(a)
        If a(i, 1) Like "*-######" Then
          If Right(a(i, 1), 6) > IDMaxSuffix Then IDMaxSuffix = Right(a(i, 1), 6)
        End If
      Next i
      For i = 1 To UBound(a)
        If Len(a(i, 1)) = 0 Then
          IDMaxSuffix = IDMaxSuffix + 1
          a(i, 1) = IDPrefix & Format(IDMaxSuffix, "000000")
        End If
      Next i
      Application.EnableEvents = False
      .Value = a
      Application.EnableEvents = True
    End With
  End If
End Sub

 

[Peter_SSs]

Forgot to say: I did assume that your prefix would still end with a "-". If not, just remove the "-" from this line

If a(i, 1) Like "*######" Then

 

[Elena Margulis]

Thank you again !!!
This is truly awesome All that I needed
(no more pc access for today for me; but I'll test the adjustments on Mon)

 

[Peter_SSs]

You're welcome. Post back if any further issues arise.

 

[Elena Margulis]

Try this version. Edit the 'Const' line near the top of the code. I have changed other parts of the code too so copy the whole lot.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim IDMaxSuffix As Long, i As Long
  Dim a As Variant
 
  Const IDPrefix As String = "ABC-2020-08-"   '<- Edit as you want
 
  If Not Intersect(Target, Range("Table1")) Is Nothing Then
    With Range("Table1[SurveyCode]")
      a = .Value
      For i = 1 To UBound(a)
        If a(i, 1) Like "*-######" Then
          If Right(a(i, 1), 6) > IDMaxSuffix Then IDMaxSuffix = Right(a(i, 1), 6)
        End If
      Next i
      For i = 1 To UBound(a)
        If Len(a(i, 1)) = 0 Then
          IDMaxSuffix = IDMaxSuffix + 1
          a(i, 1) = IDPrefix & Format(IDMaxSuffix, "000000")
        End If
      Next i
      Application.EnableEvents = False
      .Value = a
      Application.EnableEvents = True
    End With
  End If
End Sub

To Peter_Sss,
Sorry, to pulling up this subject again, but I have an additional question about this solution:
I have another Config sheet inside this Form, where I need to store text part of the SurveyCode:
as following:

Is it possible to modify your above code, so that instead

Const IDPrefix As String = "ABC-2020-08-" '<- Edit as you want

It would have sort of reference to B4 cell of the "Config" sheet, where I now store "CSS-2020-08-" ?

Something like
Const IDPrefix As String = "$B$4" ......
....
I tried different approaches, no luck ...

 

[Elena Margulis]

To Peter_Sss,
Sorry, to pulling up this subject again, but I have an additional question about this solution:
I have another Config sheet inside this Form, where I need to store text part of the SurveyCode:
as following:
View attachment 23582

Is it possible to modify your above code, so that instead

Const IDPrefix As String = "ABC-2020-08-" '<- Edit as you want

It would have sort of reference to B4 cell of the "Config" sheet, where I now store "CSS-2020-08-" ?

Something like
Const IDPrefix As String = "$B$4" ......
....
I tried different approaches, no luck ...

Actually, sorry, never mind -
Dim IDPrefix As String: IDPrefix = ThisWorkbook.Sheets("Config").Range("B4")

this worked!

 

[Peter_SSs]

I was just looking at this, but I see you got it sorted yourself.

 

[vba programming]

Hi! Please I need your help in creating Automatic ID in this format:
SAC- 01
SAC-02
SAC-03
In that order… at worksheets ( “MEMBERS DETAILS”)
At row 2, column B
(The name Manager for the table is Mlist )