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.
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.
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.
Code:
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 SubAppreciate 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.
