Hi Guys,
I have a UserForm in which an user can search a record through a "Case ID" or "Unique ID". The point here is that under a "CaseID" there could be multiple "UniqueID's" and vice versa.
When an user enters a "CaseID" the ListBox1 is required to display all "UniqueID's" falling under the particular "CaseID" and when the user selects any of the so displayed "UniqueID", the data pertaining to the particular "UniqueID" should be displayed in the relevant text boxes.
I have the below Code, however, the issue is that when I enter the CaseID (even though there are duplicates), the form does not display any information in the list box. Hence, can someone help me with this. Please note that once the data is displayed in all relevant text boxes, the same should be editable and should save whatever changes the user does in the connected excel sheet.
Private Sub TextBox3_AfterUpdate()
ListBox1.Clear
ListBox2.Clear
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.TextBox3.Value) = 0 Then
MsgBox "Please enter valid Case ID", vbExclamation
Me.TextBox3.Value = ""
Me.TextBox5.Enabled = True
Me.TextBox3.Enabled = True
Me.TextBox3.SetFocus
Exit Sub
End If
If Me.TextBox3.Text = "" Then
Exit Sub
Me.TextBox3.SetFocus
End If
Me.Label34.Caption = WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.TextBox3.Value) & " Unique ID/s found with the above Case ID"
With Me
.TextBox5.Text = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 2, 0)
.Label17 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 3, 0)
.Label18 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 4, 0)
.Label19 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 5, 0)
.Label20 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 6, 0)
.Label21 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 7, 0)
.Label22 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 8, 0)
.Label23 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 9, 0)
.Label24 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 10, 0)
.Label25 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 11, 0)
.Label26 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 12, 0)
.Label27 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 13, 0)
.Label28 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 14, 0)
.Label29 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 15, 0)
.Label30 = Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 16, 0)
.Label31 = Format(Application.WorksheetFunction.VLookup(TextBox3, Sheet2.Range("CASEID"), 17, 0), "mmm-dd-yyyy")
End With
'FOR LIST BOX
Dim lastRow As Long
Dim matchFoundIndex As Long
Dim iCntr As Long
lastRow = Range("A1048576").End(xlUp).row
For iCntr = 4 To lastRow
If Cells(iCntr, 1) = Me.TextBox3.Text And Application.WorksheetFunction.CountIf(Sheet2.Range("A3:A" & lastRow), Cells(iCntr, 1)) > 1 Then
Me.ListBox1.AddItem Cells(iCntr, 2).Value
Me.ListBox2.AddItem Cells(iCntr, 7).Value
Else
End If
Next
End Sub