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

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have data entry Worksheet (Survey), plz see below, where I need to generate unique identifier for each record (each Survey):

Capture.JPG


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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
Would you still want "-yyyy-mm-" inserted between the text and the unique number?
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Answering your question - whichever is easier to update (less work)!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
Forgot to say: I did assume that your prefix would still end with a "-". If not, just remove the "-" from this line
VBA Code:
If a(i, 1) Like "*######" Then
 

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Post back if any further issues arise. :)
 

Elena Margulis

New Member
Joined
Aug 21, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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:
Capture.JPG


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

New Member
Joined
Aug 21, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,364
Office Version
  1. 365
Platform
  1. Windows
I was just looking at this, but I see you got it sorted yourself. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,989
Messages
5,622,021
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top