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.
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