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
Display More
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
Display More
If anyone can help, it would be very much appreciated and if any further info is required please let me know.