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