VBA Inventory UserForm ID generation

[SwiftBox]

Hi all, first post! Very new to VBA so trying to watch lots of YouTube videos and browsing forums to try and understand the basics. I'm sure this has been covered before, but I seem to be going round in circles trying to find an exact answer to by issue.

I am trying to build an automated inventory where each item will be assigned a unique ID (current format used is XX123). On the userform used to enter the data, I would like the next sequential ID to be auto generated and displayed when the form is initiated. I also want this ID to then save with the data entered in the a "Database" tab. Is this possible at all?? From a previous attempt an a different inventory, I used a general iRow - 1 to use the row number as a reference number, but over time items will be removed from this new inventory, so numbers would end up being reused and not unique.

Current code for writing data to the "database" (using "Enter/Next" CommandButton so that multiple items can be entered without having to reload the UserForm. There is a modified version for the "Enter/Close" CommandButton) is;

Sub Reset()

    Dim iRow As Long

    iRow = [Counta(Database!A:A)] ' identifying the last row

    With frmAdd
    
        .TxtBatch.Value = ""
        .TxtChem.Value = ""
        .TxtComm.Value = ""
        .TxtExp.Value = ""
        .TxtMan.Value = ""
        .TxtPCode.Value = ""
        .TxtRec.Value = ""
        .TxtSup.Value = ""
        .TxtVol.Value = ""

        .ComboVol.Value = ""
        .ComboLoc.Value = ""
        .ComboTemp.Value = ""

        .ListCLP.Value = ""

        .ListGHSPhy.MultiSelect = fmMultiSelectSingle
        .ListGHSPhy.Value = ""
        .ListGHSPhy.MultiSelect = fmMultiSelectMulti

        .ListGHSHealth.MultiSelect = fmMultiSelectSingle
        .ListGHSHealth.Value = ""
        .ListGHSHealth.MultiSelect = fmMultiSelectMulti

        .ListGHSEnv.MultiSelect = fmMultiSelectSingle
        .ListGHSEnv.Value = ""
        .ListGHSEnv.MultiSelect = fmMultiSelectMulti
        
    End With
    

End Sub

Sub Submit()

    Dim Sh As Worksheet
    
    Dim iRow As Long
    
    Set Sh = ThisWorkbook.Sheets("Database")
    
    iRow = [Counta(Database!A:A)] + 1
    
    With Sh
    
        .Cells(iRow, 1) = iRow - 1
        
        .Cells(iRow, 2) = frmAdd.TxtChem.Value
        
        .Cells(iRow, 3) = frmAdd.TxtMan.Value
        
        .Cells(iRow, 4) = frmAdd.TxtPCode.Value
        
        .Cells(iRow, 5) = frmAdd.TxtVol.Value
        
        .Cells(iRow, 6) = frmAdd.ComboVol.Value
        
        .Cells(iRow, 7) = frmAdd.TxtSup.Value
        
        .Cells(iRow, 8) = frmAdd.TxtBatch.Value
        
        .Cells(iRow, 9) = frmAdd.ComboLoc.Value
        
        .Cells(iRow, 10) = frmAdd.ComboTemp.Value
        
        .Cells(iRow, 11) = frmAdd.ListGHSPhy.List(frmAdd.ListGHSPhy.ListIndex, 0)
        
        .Cells(iRow, 12) = frmAdd.ListGHSPhy.List(frmAdd.ListGHSPhy.ListIndex, 1)
                
        .Cells(iRow, 13) = frmAdd.ListGHSHealth.List(frmAdd.ListGHSHealth.ListIndex, 0)
        
        .Cells(iRow, 14) = frmAdd.ListGHSHealth.List(frmAdd.ListGHSHealth.ListIndex, 1)
        
        .Cells(iRow, 15) = frmAdd.ListGHSEnv.List(frmAdd.ListGHSEnv.ListIndex, 0)
        
        .Cells(iRow, 16) = frmAdd.ListGHSEnv.List(frmAdd.ListGHSEnv.ListIndex, 1)
        
        .Cells(iRow, 17) = frmAdd.ListCLP.List(frmAdd.ListCLP.ListIndex, 0)
        
        .Cells(iRow, 18) = frmAdd.ListCLP.List(frmAdd.ListCLP.ListIndex, 1)
        
        .Cells(iRow, 19) = frmAdd.ListCLP.List(frmAdd.ListCLP.ListIndex, 2)
        
        .Cells(iRow, 20) = frmAdd.TxtRec.Value
        
        .Cells(iRow, 21) = frmAdd.TxtExp.Value
        
        .Cells(iRow, 22) = frmAdd.TxtComm.Value
                       
        .Cells(iRow, 23) = Application.UserName
        
        .Cells(iRow, 24) = [Text(Now(),"DD/MM/YYYY HH:MM:SS")]
                
            
    
    End With
    
    
    frmAdd.Hide
    

End Sub


Sub Show_form()


    frmAdd.Show
    
End Sub

Any help will be appreciated!

 

[Herakles]

When you say that the current format used is XX123, are you prepared to change this format?

Do you mean XX999 or AA001 to ZZ999?

I normally use a genuinely unique ID that does not follow a sequence.

This function will create an ID of 8, 9 or 10 characters in length.

Public Function fncUniqueID(intLength As Integer)
    
    fncUniqueID = WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 4294967295#), intLength)

End Function