TextBox dependent Unique ID (Count through multiple tables)

[Icesurf3r]

I am creating a userform to record incoming business opportunities across multiple work-streams and need help with the Unique ID feature of the coding (appreciate this is a common question and I have looked, and looked again and Googled - but I couldn't find anything for my situation) as I'm using tables rather than worksheets.

My workbook structure is as follows:
Sheet 1 (Tracker) Table_Main
Sheet 2 (Go) Table_Go
Sheet 3 (No-Go) Table_NoGo

All the tables start with a header row in B2. Row 1 and Column A are blank.

On the Userform I have a textbox "Discipline", and to create the unique ID I would like to use the first three letters from the value of the "Discipline" text box (which contains 12 different disciplines) followed by a hyphen and then a three digit number i.e ABC-001, ABC-002, DEF-001 etc. Which will be saved into column 'C'.

So if the same discipline is selected the count increases by +1 for that discipline, but each disciplines count should start from 1 (if that makes sense).

Now the complicated part (not that it isn't all complicated - well for me anyway) When calculating the next number in the sequence I need to search the unique ID column of each table because as the opportunity progresses it will be moved to a different table. I also need to maintain the integrity of the count when closing and reopening the UserForm so they don't start from 1 again.

Below is the code I'm currently using to copy the Userform Data to the Table_Main and all the above needs to be part of the same button click.

Sub Add_Record()'Copy input values to Main Table
    Dim oNewRow As ListRow
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Tracker").Range("Table_Main")
    rng.Select
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    With ws
        oNewRow.Range.Cells(1, 1).Value = UserForm1.DTPicker1.Value
        oNewRow.Range.Cells(1, 3).Value = UserForm1.ComboBox_Lots.Value
        oNewRow.Range.Cells(1, 4).Value = UserForm1.Discipline.Value
        oNewRow.Range.Cells(1, 5).Value = UserForm1.PrimaryLead1.Value
        oNewRow.Range.Cells(1, 6).Value = UserForm1.SecondaryLead1.Value
        oNewRow.Range.Cells(1, 7).Value = UserForm1.CbOpType.Value
        oNewRow.Range.Cells(1, 8).Value = UserForm1.TbOpName.Value
        oNewRow.Range.Cells(1, 9).Value = UserForm1.TbClientName.Value
        oNewRow.Range.Cells(1, 10).Value = UserForm1.CbDecision.Value
        oNewRow.Range.Cells(1, 11).Value = UserForm1.Tb_Notes.Value
    End With
    MsgBox "Opportunity has been added to Tracker", vbOKOnly
    Clear_Form
End Sub

Appreciate this is a big ask, but I would be very appreciative of any help received as I have only "Dabbled" in VBA and that was a few years ago, so I'm almost starting from scratch again.

 

[NoSparks]

Use

 Displn = Left(Discipline.value, 3) & "*"

and

 MsgBox Left(Discipline.value, 3) & "-" & Format(IDnum, "000")

 

[Icesurf3r]

Thanks NoSparks.

Have done some testing this morning and it's now working like a charm.

Appreciate your help and effort with this one, thanks again.

 

[NoSparks]

You're welcome, glad I could be of assistance.