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.)

    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:

    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.

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!