Search and Match Userform Fields in Worksheet and Show Results in Listbox

[ExcelEndeavor]

I have a userform that I use to track budget items. The screenshot below shows a blank form that is used to enter in the information, as well as edit existing info. Invoices are entered and submitted through another userform that pops up when the user clicks "Add Invoice" and submits from there. Those details are sent to a worksheet called "PO Data" along with 3 fields from the userform below -

Team Lead (column A)
Business Unit (column B)
Payee (column C)

I would like to search the worksheet "PO Data" (columns A:C) for any matches across those 3 fields and return the invoice results (columns E:H) back to the listbox highlighted in red.
For example, any maches where Stacy from the Sales Dept with a PO from vendor Adobe, I would like the invoice details to show in the listbox in real time.
It would make more sense to just search for the PO number, but some vendors don't issue a PO - they just use our corp credit card on file, so there are several "cc" instances in the PO number column.

How would I accomplish the search, match, and return ?

 

[myall_blues]

Can you clarify what you mean by 'in real time'? Do you mean it should update that listbox as the user enters details of the team lead, business unit and payee, or is it to be linked to the 'Find Existing' button?

 

[myall_blues]

Assuming linking it to the 'Find Existing' button, then something like this, where:
CommandButton1 is the 'Find Existing' button name
TextBox1 contains the Team Lead name
TextBox2 contains the Business Unit
TextBox3 contains the Payee

Private Sub CommandButton1_Click()
    Dim Rng As Range
    Dim TLead As Range
    Dim c As Long
    Dim arrCount As Integer
    Set Rng = Sheets("PO Data").Range("A1:A1000")
    ReDim arr(1 To 4, 1 To Rng.Count)
    For Each TLead In Rng
        If TLead.Value = TextBox1.Value And Cells(TLead.Row, 2).Value = TextBox2.Value And Cells(TLead.Row, 3) = TextBox3.Value Then
            c = c + 1
            For arrCount = 1 To 4
                arr(arrCount, c) = TLead.Offset(, 3 + arrCount)
            Next arrCount
        End If
    Next TLead
    ReDim Preserve arr(1 To 4, 1 To c)
    Me.ListBox1.List = Application.Transpose(arr)
End Sub

 

[ExcelEndeavor]

I think I made my request too vague and complicated, so let me break it down here...


1. This is what the RequestForm looks like when it is completed. To add an invoice, the user will click the "Add Invoice" button (highlighted in red):

2. Then this AddInvoiceForm opens where invoice details can be entered:

3. When the "Add" button is clicked, the information is sent to a worksheet "PO Data":

Private Sub ButtonAddInvoice_Click()

'Declare worksheet variable
Dim rSh As Worksheet
On Error Resume Next
Set rSh = ThisWorkbook.Sheets("PO Data")
On Error GoTo 0


'If it is a new record
'Get the next available row
Dim nextRow As Long
If Label2 = "True" Then
      nextRow = updateRow


MsgBox "Successfully Added"
Unload Me

Else
      nextRow = rSh.Range("A" & Rows.Count).End(xlUp).Row + 1
End If


'Assign columns
rSh.Range("A" & nextRow).Value = RequestForm.TxtTeamLead
rSh.Range("B" & nextRow).Value = RequestForm.ComboBU
rSh.Range("C" & nextRow).Value = RequestForm.TxtPayee
rSh.Range("D" & nextRow).Value = RequestForm.TxtPONbr
rSh.Range("E" & nextRow).Value = TxtInvoiceNumber
rSh.Range("F" & nextRow).Value = TxtInvoiceDate
rSh.Range("G" & nextRow).Value = TxtInvoiceAmount
rSh.Range("H" & nextRow).Value = CheckboxPaidInvoice

'Unload Me
clearForm
Unload Me

End Sub

___________________________________________


Sub clearForm()

TxtInvoiceNumber = ""
TxtInvoiceDate = ""
TxtInvoiceAmount = ""
CheckboxPaidInvoice = ""

End Sub

4. That window will close and now I would like the invoice details to automatically populate in the listbox (the red arrow below) by matching the three data points (highlighted in red) in the "PO Data" worksheet

rSh.Range("A" & nextRow).Value = RequestForm.TxtTeamLead
rSh.Range("B" & nextRow).Value = RequestForm.ComboBU
rSh.Range("C" & nextRow).Value = RequestForm.TxtPayee

... and return the four data points listed in the listbox header

rSh.Range("E" & nextRow).Value = TxtInvoiceNumber
rSh.Range("F" & nextRow).Value = TxtInvoiceDate
rSh.Range("G" & nextRow).Value = TxtInvoiceAmount
rSh.Range("H" & nextRow).Value = CheckboxPaidInvoice

Hopefully this helps better describe what I'm looking for.

 

[ExcelEndeavor]

Assuming linking it to the 'Find Existing' button, then something like this, where:
CommandButton1 is the 'Find Existing' button name
TextBox1 contains the Team Lead name
TextBox2 contains the Business Unit
TextBox3 contains the Payee

Private Sub CommandButton1_Click()
    Dim Rng As Range
    Dim TLead As Range
    Dim c As Long
    Dim arrCount As Integer
    Set Rng = Sheets("PO Data").Range("A1:A1000")
    ReDim arr(1 To 4, 1 To Rng.Count)
    For Each TLead In Rng
        If TLead.Value = TextBox1.Value And Cells(TLead.Row, 2).Value = TextBox2.Value And Cells(TLead.Row, 3) = TextBox3.Value Then
            c = c + 1
            For arrCount = 1 To 4
                arr(arrCount, c) = TLead.Offset(, 3 + arrCount)
            Next arrCount
        End If
    Next TLead
    ReDim Preserve arr(1 To 4, 1 To c)
    Me.ListBox1.List = Application.Transpose(arr)
End Sub

that didn't work - I posted a clarifying response in the main string because I think my original post was confusing.

 

[dmt32]

I posted a clarifying response in the main string because I think my original post was confusing.

Still not fully clear to me but If you just want to populate your listbox with specific dater after posting to the range then maybe something like this will do what you want

    rSh.Range("E" & nextRow).Value = Me.TxtInvoiceNumber
    rSh.Range("F" & nextRow).Value = Me.TxtInvoiceDate
    rSh.Range("G" & nextRow).Value = Me.TxtInvoiceAmount
    rSh.Range("H" & nextRow).Value = Me.CheckboxPaidInvoice
    
   With Me.ListBox1
    .ColumnCount = 4
    .AddItem Me.TxtInvoiceNumber
    .List(.ListCount - 1, 1) = Me.TxtInvoiceDate
    .List(.ListCount - 1, 2) = Me.TxtInvoiceAmount
    .List(.ListCount - 1, 3) = IIf(Me.CheckboxPaidInvoice.Value, "Paid", "Not Paid")
  End With

Change name of listbox as required.

If this is not what you seek then it would assist forum if you could place copy of your workbook (with dummy data) on a file sharing site like Dropbox & provide a link to it here.
In addition, some examples of expected results would also help those who may be able to offer solutions.

Hope Helpful

Dave

Dave

 

[Peter_SSs]

Reminder:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Search Worksheet and Return Specific Values to a Listbox
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.