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

ExcelEndeavor

New Member
Joined
Oct 13, 2020
Messages
34
Office Version
  1. 365
Platform
  1. MacOS
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 ?


Invoice - Record.jpg
 
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?
 
Upvote 0
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

VBA Code:
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
 
Upvote 0
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):

2025-03-06_06-14-19.jpg



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

2025-03-06_06-15-51.jpg



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

VBA Code:
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

Copy of 2025-03-06_06-17-10.jpg



Hopefully this helps better describe what I'm looking for.
 
Upvote 0
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

VBA Code:
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.
 
Upvote 0
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

Rich (BB code):
    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
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top