List All Matches in textbox VBA

new11

New Member
Joined
Sep 15, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I was just wondering if anyone might know the answer to a little problem I'm having below?

I have a userform for generating invoices that has a combobox (cbo_clientnr) that allows a client number to either be selected or searched for. I also have a checkbox (check_outstanding) that when pressed will search through a payment database to find a matching client number for any invoices that haven't been paid, and then sums all invoice amounts together for all matches found. Additionally, it also displays the invoice name in another textbox (tb_invoiceoutstanding)

This works fine but what I'm struggling to figure out is how to list all invoice numbers in the textbox (tb_invoiceoutstanding) at the moment it will only list 1 invoice number in the textbox.
Does anyone know how I am able to list all the invoice numbers in the textbox rather than only 1?


Any assistance or advice in the right direction would be really appreciated.
I hope my question makes sense, this was the only way I could think of explaining it.


VBA Code:
Private Sub check_outstanding_Click()

Dim payment_db As Worksheet
    Set payment_db = Sheets("payment_db")

Dim c As Long
Dim Rng As Range, Sel As Variant
   
    Sel = Me.cbo_clientnr.Value
    Set Rng = payment_db.Columns(4).Find(Sel, lookat:=xlWhole)

''##### if any of the cells in column"21U" is blank or contain "n or no" then start "Sel"  ########
    For c = 2 To payment_db.Range("D10000").End(xlUp).Row
        If payment_db.Cells(c, 4) = cbo_clientnr Then
            If payment_db.Cells(c, 21) = "" Or payment_db.Cells(c, 21) = "n" Or payment_db.Cells(c, 21) = "NO" Or payment_db.Cells(c, 21) = "no" _
                Or payment_db.Cells(c, 21) = "N" Or payment_db.Cells(c, 21) = "No" Then
                    If Sel <> "" Then    '###### using "cbo_clientnr" look for and find match in column "D" and then sum all matching values from column "I"  ############
                    If Not Rng Is Nothing Then
                                If check_outstanding = True Then
                                Me.existing_charge.Value = WorksheetFunction.SumIf(payment_db.Range("D:D"), Me.cbo_clientnr, payment_db.Range("I:I"))    '''### column D = client number to match with "cbo_clientnr" column I = invoice total to sum #####
                                    tb_invoiceoutstanding = payment_db.Cells(Rng, "B")   ''##### This is where I am trying to have all the matching invoice numbers get [I]listed (at the moment it only lists 1)[/I]
                                Else
                                If check_outstanding = False Then
                                existing_charge = ""
                                tb_invoiceoutstanding = ""
                                End If
                                End If
        End If
        End If
        End If
        End If
    Next c

End Sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,798
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
most welcome. Appreciate feedback

Dave
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,181,615
Messages
5,930,962
Members
436,767
Latest member
Langaws

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
Top