TextBox dependent Unique ID (Count through multiple tables)

Icesurf3r

New Member
Joined
Feb 13, 2013
Messages
39
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.

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 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.
 
Use
Code:
 Displn = Left(Discipline.value, 3) & "*"
and
Code:
 MsgBox Left(Discipline.value, 3) & "-" & Format(IDnum, "000")
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top