Help With _AfterUpdate function linked to CountIf

Hi

I'm fairly new to VBA, having dabbled slightly several years ago and I find myself having a couple of issues with my worksheet that are interlinked. (generate a unique ID (counting across multiple tables on different worksheets, then write that value to a textbox on the userform.)

Background
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
Sheet 4 (Opt-In) Table_OptIn
Sheet 5 (Opt-Out) Table_OptOut

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" which is populated from a ComboBox selection using the below code:

Private Sub ComboBox_Lots_Change()


'Populates Primary Lead, Secondary Lead and Discipline text boxes based on ComboBox selection
If ComboBox_Lots.ListIndex > -1 Then
        PrimaryLead1.Value = Sheets("Lists").Range("Lots")(ComboBox_Lots.ListIndex + 1).Offset(, 4).Value
        SecondaryLead1.Value = Range("Lots")(ComboBox_Lots.ListIndex + 1).Offset(, 6).Value
        Discipline.Value = Range("Lots")(ComboBox_Lots.ListIndex + 1).Offset(, 2).Value
        CbOpType.Enabled = True
        CbOpType.Value = "Please Select"
        CbOpType.List = Sheets("Lists").Range("Type").Value
    Else
        PrimaryLead1.Value = ""
        SecondaryLead1.Value = ""
        Discipline.Value = ""
    End If
End Sub

What I'm trying to achieve is to generate a unique ID consisting of the first three letters of the text in the Discipline TextBox (which could be one of 12 different disciplines) followed by a hyphen 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).

To complicate matters a little, I would need to search through the Unique ID Column (which is the 3rd excel column 'C', but the second table column) of all the tables because as the opportunity progresses it will be moved to a different table. Once the count is complete and the unique ID generated it would appear in the correct textbox "Ref_Num" on the UserForm

I have tried the code below but I get an Run-Time error '5': Invalid procedure call or argument error.

Private Sub Discipline_AfterUpdate()
   'To generate unique ID number
   Dim IDnum As Long, Displn As String
   Displn = Left(Me.Discipline.Value, 3)
   IDnum = WorksheetFunction.CountIf(Sheets("Tracker").ListObject("Table_Main").ListColumns(2).DataBodyRange, Displn) + _
            WorksheetFunction.CountIf(Sheets("Go").ListObject("Table_Go").ListColumns(2).DataBodyRange, Displn) + _
            WorksheetFunction.CountIf(Sheets("Opt-In").ListObject("Table_OptIn").ListColumns(2).DataBodyRange, Displn) + _
            WorksheetFunction.CountIf(Sheets("Opt-Out").ListObject("Table_OptOut").ListColumns(2).DataBodyRange, Displn) + _
            WorksheetFunction.CountIf(Sheets("No-Go").ListObject("Table_NoGo").ListColumns(2).DataBodyRange, Displn) + 1
    Me.Ref_Num.Value =  Displn & "-" & Format(IDnum, "000")
End Sub

If anyone can help, it would be very much appreciated and if any further info is required please let me know.

Thank you for the comment Roy.

Unfortunately, that is one part I do not have control over, so it has to be multiple tables in multiple worksheets.

I have also posted the question on:
https://www.mrexcel.com/forum/…ough-multiple-tables.html

Where a solution is being worked on, so if I can marked this post as closed to avoid duplication of work, it would be appreciated.

Thanks Again.