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]

Try the following to replace all code in the download file :

Option Explicit

Declare Function CoCreateGuid Lib "ole32" (ByRef GUID As Byte) As Long

Public Function GetNewGuid() As String
    Dim ID(0 To 15) As Byte
    Dim N As Long
    Dim GUID As String
    Dim Res As Long
    Res = CoCreateGuid(ID(0))

    For N = 0 To 15
        GUID = GUID & IIf(ID(N) < 16, "0", "") & Hex$(ID(N))
        If Len(GUID) = 8 Or Len(GUID) = 13 Or Len(GUID) = 18 Or Len(GUID) = 23 Then
            GUID = GUID & "-"
        End If
    Next N
    GetNewGuid = GUID
End Function

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

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

 

[rjmdc]

your video shows it working. nothing happens

 

[rjmdc]

is there a difference for 64 bit. this code has alert that it wont adapt to 64bit
thanks

 

[NoSparks]

Using the function from this Excel Titan site this should work for both 32 and 64 bit.

In the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 And Target.CountLarge = 1 And Target.Offset(, 19).Value = "" Then
        Target.Offset(, 19).Value = CreateGuidString(True, False)
    End If
    
End Sub

In a standard module

'==================================================================================================
' ## Declarations for the GUID type and for Windows API
'==================================================================================================
    Private Type GUID_TYPE
        '// Vars
        Data1 As Long
        Data2 As Integer
        Data3 As Integer
        Data4(7) As Byte
    End Type

    '// Test for 32 or 64 bit Excel
    #If VBA7 Then
        Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As LongPtr
        Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As LongPtr
    #Else
        Private Declare Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As Long
        Private Declare Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpStrGuid As LongPtr, ByVal cbMax As Long) As Long
    #End If
    
'==================================================================================================
' ## Function to call Windows API and grab a GUID
'    Using this method as July 2017 Windows 10 security update
'    throws a permission denied error trying to use: CreateObject("Scriptlet.TypeLib")
'==================================================================================================
Function CreateGuidString(Optional AddHyphens As Boolean, _
                          Optional AddBraces As Boolean) _
                          As String
    '// Vars
    Dim guid As GUID_TYPE
    Dim strGuid As String
    Dim retValue As LongPtr

    '// registry GUID format with null
    '   terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
    Const guidLength As Long = 39

    retValue = CoCreateGuid(guid)

    '// Get the raw GUID which includes braces and hyphens
    If retValue = 0 Then
        strGuid = String$(guidLength, vbNullChar)
        retValue = StringFromGUID2(guid, StrPtr(strGuid), guidLength)
        If retValue = guidLength Then
            CreateGuidString = strGuid
        End If
    End If

    '// If AddHyphens is switched from the default True to False,
    '   remove them from the GUID
    If Not AddHyphens Then
        CreateGuidString = Replace(CreateGuidString, "-", vbNullString, Compare:=vbTextCompare)
    End If

    '// If AddBraces is True from the default False to True,
    '   leave those curly braces be!
    If Not AddBraces Then
        CreateGuidString = Replace(CreateGuidString, "{", vbNullString, Compare:=vbTextCompare)
        CreateGuidString = Replace(CreateGuidString, "}", vbNullString, Compare:=vbTextCompare)
    End If
End Function

 

[Logit]

.
The following example workbook may be downloaded from this URL : Auto Increment Col T.xlsb

The macro code contained within the workbook has been tested on Excel 2007, 2010, 2013, 2016. It works successfully on all versions.
Additionally, the code was run on Window 10, Windows 8, Windows 7 and Windows XP. It ran flawlessly on all versions.

The downloaded workbook example functions successfully here after downloading it on several different computers. The macro code does not
depend on API Calls, Formulas (in cells), GUID commands or anything higher in complexity than very basic, simple macro coding. Your project will
not be required to reference any .DLL's, .COM files, the Windows Registry or specialized commands.


COMMAND BUTTON AND MACRO

Create a Command Button on the Worksheet to be utilized. Connect that button to the macro. If you are not familiar how to do these two steps, please Google
"Excel Create Command Button" ... you can also Google "Excel Attach Button To Macro". You will be provided with a plethora of example directions including YouTube
videos that will guide you step by step through the process.


MACRO CODE

Here is the macro code that is located in a REGULAR MODULE :

Option Explicit

Sub AutoIncremntColT()
Dim i As Integer
Dim cell As Range, rng As Range

Set rng = Range("T5:T5000")  'Edit range here

i = 1

For Each cell In rng
    cell.Value = "U-" & Year(Date) & "-000" & i
    i = i + 1
Next cell
End Sub

Here is an image, for visual reference, of attaching the Button to the Macro :

Here is an image, for visual reference, of the Macro :

Hopefully reviewing these two images will assist you in the correct steps of creating the button and pasting the macro.


NOTE : THIS MACRO SHOULD ONLY BE RUN ONCE.

It will provide your project with 5000 unique ID's for your project in Col T beginning at row #5.

After completing the above, the user only need enter the Customer Name or other appropriate data in Column A.
The above macro is not dependent on other macros or user actions.


RECOMMENDATION

After running the macro, it is highly recommended the COMMAND BUTTON be removed from the Worksheet. This will prevent accidental
changes to the newly created unique ID's in Column T.

The Command Button may be removed by RIGHT CLICKING the button, then selecting CUT.

If it is necessary to recreate the unique ID's in Column T, the COMMAND BUTTON may be recreated, attached to the macro and run the
macro again.