Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

SEARCH AND UPDATE

0

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

Answer
Discuss

Discussion

So, is the problem simply in populating the listbox with all matches?
don (rep: 1989) Aug 1, '16 at 1:30 pm
Hi Don,

Yes, it is a problem of populating the List Box.
pintoshawn Aug 2, '16 at 8:23 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login