create unique ID like a number or number letter combo for each new entry

[rjmdc]

what vba would i use to add a unique transaction ID as soon as first column in the row is filled in. this id needs to be unchanging no matter what i do to this row
it also needs to stay static even if i sort or group.
please advise and help.
also this sheet already has many other vba so where would i add it

 

[Logit]

.
The following will generate a UNIQUE ID (then alphanumeric characters). It is presently set for cell A1. That can be
edited as required.

Option Explicit

Sub farhadj()
If Sheet1.Range("A1").Value <> "" Then Exit Sub
  With Range("A1")
    .Formula = "=DecToBij(RANDBETWEEN(1727605,62193780), ""^1$3%5(7&9AbCdEfGhIjKlMnOpQrStUvWxYz"")" & _
               "&DecToBij(RANDBETWEEN(1727605,62193780), ""@2#4^6*8)0aBcDeFgHiJkLmNoPqRsTuVwXyZ"")"
    .Value = .Value
  End With

End Sub

Function DecToBij(iNum As Long, sSym As String) As String
 
  If iNum > 0 Then DecToBij = DecToBij((iNum - 1) \ Len(sSym), sSym) & _
     Mid(sSym, ((iNum - 1) Mod Len(sSym)) + 1, 1)
End Function

 

[rjmdc]

this would increase and add a unique id as a field row is added?
i can sort and the number stays with the field?
i can delete rows and the numbers still stay?
explain please
"=DecToBij(RANDBETWEEN(1727605,62193780), ""^1$3%5(7&9AbCdEfGhIjKlMnOpQrStUvWxYz"")"

 

[rjmdc]

sorry
how would i amend /add to your code, i cant get this to work for me

target = column A
if target <> ""
then continue on into your code to add id to column T

 

[Logit]

In the Worksheet_Change event, make a call to the macro so it will run :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
farhadj
End Sub

Change the Cell reference as required :

Sheet1.Range("A1").Value '<--- change to the cell you want the unique ID to appear.

 

[rjmdc]

i am so sorry this doesnt work

 

[rjmdc]

can anyone else help me
workbook has a code to backup on the workbook
then sheet 1 has 3 macros already each one when a different cell is activated
i need that as soon as i fill in a name in column A then column T needs to fill automatically with ID #
this needs to be a permanent ID number for this entry no matter how i change or manipulate or sort it

 

[Logit]

.
Paste the following into a REGULAR MODULE :

Option Explicit

Public Function CreateGUID(Optional IncludeHyphens As Boolean = True, _
                           Optional IncludeBraces As Boolean = False) _
                           As String
   
    Dim obj As Object
    Dim strGUID As String
   
    Set obj = CreateObject("Scriptlet.TypeLib")
   
    'Assign the raw GUID, minus the last two erroneous chars
    strGUID = Left(obj.GUID, Len(obj.GUID) - 2)
   
    'If IncludeHyphens is switched from the default True to False,
    'remove them from the GUID
    If Not IncludeHyphens Then
        strGUID = Replace(strGUID, "-", vbNullString, Compare:=vbTextCompare)
    End If
   
    'If IncludeBraces is switched from the default False to True,
    'leave those curly braces be!
    If Not IncludeBraces Then
        strGUID = Replace(strGUID, "{", vbNullString, Compare:=vbTextCompare)
        strGUID = Replace(strGUID, "}", vbNullString, Compare:=vbTextCompare)
    End If
   
    CreateGUID = strGUID
   
End Function

Sub Test()
Dim GUID As String
On Error Resume Next

    GUID = CreateGUID() '<~ default
   
    If ActiveCell.Column <> 2 Then
        'ActiveCell.Offset(0, 18).Value = ""
        Exit Sub
    Else
        If ActiveCell.Offset(0, -1) <> "" Then
            ActiveCell.Offset(0, 18).Value = GUID
        End If
    End If
   
End Sub

Paste the following in the SHEET LEVEL MODULE :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Test
End Sub

In your previous posts, you failed to mention this :

" i need that as soon as i fill in a name in column A then column T needs to fill automatically with ID # "

That small bit of information made all the difference. Consider this in future posts.

 

[rjmdc]

ok
please explain where these codes go, how would i use them
i have a workbook save code on the workbook tab
then on sheet 1 i have all these codes
Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target 'event runs when cell in Column G or column H is changed
Macro2 Target 'event runs when cell in Column E is changed
Macro3 Target 'event runs when cell in Column C is changed
Macro4 Target 'event runs when cell in Column A is changed

then i use each sub for ex.
Private Sub Macro1(ByVal Target As Range)

 

[Logit]

.
Download working file here : Auto File Col T From Col A.xlsb