how show data in listbox and calculate balances customers

[MKLAQ]

hi
I have data in sheet data . indeed I 've found many codes to how shows data in listbox when search the customers . but the challenge is how calculate the balance . it's not like normal way . so I put the expected result in sheet LISTBOX when search customer SAMMER how should be in listbox and show the TOTAL VALUES DEBIT (TEXTBOX2) by summing the values in COL DEBIT and the same thing TOTAL values CREDIT(TEXTBOX3) but the BALANCE(TEXTBOX4) should subtract DEBIT (TEXTBOX2) from CREDIT(TEXTBOX3) if the textbox3 . when gives minus value should highlight by red color and the COL BALANCE in listbox should show like what I put in sheet LISTBOX. see the formula how should be in last COLUMN BALANCE . with considering there is no sheet LIST at all just to understand how should show in listbox. last thing I want when run user form show all of data as in sheet data and calculate the values in textbox2,3,4
thanks in advance

[MKLAQ]

any suggestion experts?

[nankw83]

Hi MKLAQ,

Not sure what is your final expected result but check the below code & attached file. This should be sufficient to get you started ...

Private Sub UserForm_Initialize()

Dim a: a = Sheets("DATA").[A1].CurrentRegion

With CreateObject("System.Collections.ArrayList")
   For x = 2 To UBound(a)
      If Not .Contains(a(x, 3)) Then .Add a(x, 3)
   Next
   .Sort
   ListBox1.List = .ToArray
   ListBox1.ListIndex = 0
End With

End Sub

Private Sub ListBox1_Click()

With Sheets("DATA").[A1].CurrentRegion
   TextBox1 = ListBox1.Value
   TextBox2 = Application.SumIf(.Columns(3), ListBox1.Value, .Columns(6))
   TextBox3 = Application.SumIf(.Columns(3), ListBox1.Value, .Columns(7))
   TextBox4 = Val(TextBox2) - Val(TextBox3)
End With

End Sub

[MKLAQ]

it gives automation error in this line

[nankw83]

In my file, replace the code in the user form with the below ...

Private Sub UserForm_Initialize()

Dim a: a = Sheets("DATA").[A1].CurrentRegion

With CreateObject("scripting.dictionary")
   For x = 2 To UBound(a)
      If Not .exists(a(x, 3)) Then .Add a(x, 3), Nothing
   Next
   ListBox1.List = .keys
   ListBox1.ListIndex = 0
End With

End Sub

Private Sub ListBox1_Click()

With Sheets("DATA").[A1].CurrentRegion
   TextBox1 = ListBox1.Value
   TextBox2 = Application.SumIf(.Columns(3), ListBox1.Value, .Columns(6))
   TextBox3 = Application.SumIf(.Columns(3), ListBox1.Value, .Columns(7))
   TextBox4 = Val(TextBox2) - Val(TextBox3)
End With

End Sub

[MKLAQ]

thanks for your trying , but you mistunderstood me when I put SAMMER in sheet listbox this example to search by write in textbox1 when search this name and show all of data into listbox as is in sheet listbox .
your code shows one column the names and I have to select name to show the values in textboxes . short word it should all shows the columns into listbox based on search name in textbox 1 then shoould calculate the last column as I put in sheet listbox and show the values in other textboxes

[MKLAQ]

two cases :
when run userform should show all of data and calculate
and wen search for specific name

[jindon]

How can anybody even guess how you want it without detailed explanation?

Option Explicit

Private Sub UserForm_Initialize()
    Dim a, i As Long, Cr As Double, Dr As Double
    With Sheets("data").Cells(1).CurrentRegion
        a = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
    For i = 1 To UBound(a, 1)
        a(i, UBound(a, 2)) = a(i, 6) + a(i, 7)
        Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
    Next
    With Me.ListBox1
        .ColumnCount = UBound(a, 2)
        .List = a
    End With
    Me.TextBox2 = Format$(Cr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox4 = Format$(Cr - Dr, "#,#.00;-#,#.00;0")
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim a, x, i As Long, Cr As Double, Dr As Double
    Me.ListBox1.Clear
    If Me.TextBox1.Value = "" Then UserForm_Initialize: Exit Sub
    With Sheets("data").Cells(1).CurrentRegion
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(3).Address & _
        "=""" & Me.TextBox1 & """,row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then a = Application.Index(.Value, _
        Application.Transpose(x), Evaluate("column(" & .Rows(1).Address & ")"))
    End With
    If Not IsArray(a) Then Exit Sub
    If UBound(x) = 0 Then
        ReDim Preserve a(1 To UBound(a) + 1)
        a(UBound(a)) = Application.Sum(Application.Index(a, Array(6, 7)))
        Cr = a(6): Dr = a(7)
        Me.ListBox1.Column = a
    Else
        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
        For i = 1 To UBound(a, 1)
            a(i, UBound(a, 2)) = a(i, 6) + a(i, 7)
            Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
        Next
        Me.ListBox1.List = a
    End If
    Me.TextBox2 = Format$(Cr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox4 = Format$(Cr - Dr, "#,#.00;-#,#.00;0")
End Sub

[MKLAQ]

How can anybody even guess how you want it without detailed explanation?

thanks for this note . but I attched the images and explain the result data in sheet listbox how should show the data in listbox .
with all of this means I'm not at good writing by english to understand me.
any way thanks for your help , but the values in last column into listbox are wrong see the picture

the right value in last column should be like this
4000-2000= 3,800 not 4,200
and 3,800-2500=1300 not 2500
and 1300-2501=-1201 not 2501
-1201+100=-101 not 100
as you see every time subtract between debit and credit . it will take the last balance and summing or subtracting the next row in listbox
and when gives minus value in textbox BALANCE should highlight by red

[jindon]

That's not the skill of English, but the willingness to covey what you are thinking to someone else.
When you can not tell what you think of in English, upload a workbook that shows the before and detailed after sheet(s) to avoid any confusion.

That's your least responsibility, not ours.

1) Change in Initialize Event code

    For i = 1 To UBound(a, 1)
        a(i, UBound(a, 2)) = a(i, 6) + a(i, 7)
        Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
    Next

to

    For i = 1 To UBound(a, 1)
        a(i, UBound(a, 2)) = a(i, 6) - a(i, 7)
        Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
    Next

2) TextBox1_Exit event code

        a(UBound(a)) = Application.Sum(Application.Index(a, Array(6, 7)))
        Cr = a(6): Dr = a(7)

to

        Cr = a(6): Dr = a(7)
        a(UBound(a)) = Cr - Dr

        For i = 1 To UBound(a, 1)
            a(i, UBound(a, 2)) = a(i, 6) + a(i, 7)
            Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
        Next

to

        For i = 1 To UBound(a, 1)
            a(i, UBound(a, 2)) = a(i, 6) - a(i, 7)
            Cr = Cr + a(i, 6): Dr = Dr + a(i, 7)
        Next

[MKLAQ]

thanks but the balance not show as what I want . the last column in BLANCE in listbox should be
3800
1300
-1201
-1101
th formule in last column should take the balance and add the debit and subtract credit in next row . the same thing the others balance should move the next row and calculate among balance ,debit,credit
also as I said when th balance (textbox4)=minus value then should highlight by red
thanks again

[jindon]

What those figures for?

If you are testing with the different workbook, HOW CAN WE KNOW?

[MKLAQ]

th formule in last column should take the balance and add the debit and subtract credit in next row . the same thing the others balance should move the next row and calculate among. I 'm talking about just when search in specific name .
if it's not clear . I will apply inside the sheet and attach the file

[jindon]

I took Dr/Cr in other way around. so

Option Explicit

Private Sub UserForm_Initialize()
    Dim a, i As Long, Cr As Double, Dr As Double
    With Sheets("data").Cells(1).CurrentRegion
        a = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
    For i = 1 To UBound(a, 1)
        a(i, UBound(a, 2)) = a(i, 6) - a(i, 7)
        Dr = Dr + a(i, 6): Cr = Cr + a(i, 7)
    Next
    With Me.ListBox1
        .ColumnCount = UBound(a, 2)
        .List = a
    End With
    Me.TextBox2 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Cr, "#,#.00;-#,#.00;0")
    With Me.TextBox4
        .ForeColor = vbBlack
        .Value = Format$(Dr - Cr, "#,#.00;-#,#.00;0")
        If Dr - Cr < 0 Then .ForeColor = vbRed
    End With
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim a, x, i As Long, Cr As Double, Dr As Double
    Me.ListBox1.Clear
    If Me.TextBox1.Value = "" Then UserForm_Initialize: Exit Sub
    With Sheets("data").Cells(1).CurrentRegion
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(3).Address & _
        "=""" & Me.TextBox1 & """,row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then a = Application.Index(.Value, _
        Application.Transpose(x), Evaluate("column(" & .Rows(1).Address & ")"))
    End With
    If Not IsArray(a) Then Exit Sub
    If UBound(x) = 0 Then
        ReDim Preserve a(1 To UBound(a) + 1)
        a(UBound(a)) = Application.Sum(Application.Index(a, Array(6, 7)))
        Dr = a(6): Cr = a(7)
        a(UBound(a)) = Dr - Cr
        Me.ListBox1.Column = a
    Else
        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
        For i = 1 To UBound(a, 1)
            a(i, UBound(a, 2)) = a(i, 6) - a(i, 7)
            Dr = Dr + a(i, 6): Cr = Cr + a(i, 7)
        Next
        Me.ListBox1.List = a
    End If
    Me.TextBox2 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Cr, "#,#.00;-#,#.00;0")
    With Me.TextBox4
        .ForeColor = vbBlack
        .Value = Format$(Dr - Cr, "#,#.00;-#,#.00;0")
        If Dr - Cr < 0 Then .ForeColor = vbRed
    End With
End Sub

[MKLAQ]

see this file sheet LISTBOX in COL H this is should show in listbox

[MKLAQ]

I tested in post#14 . this is the same thing about the calculate
plese see post#15
thanks again

[jindon]

Replace TextBox1_Exit code with

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim a, x, i As Long, Cr As Double, Dr As Double
    Me.ListBox1.Clear
    If Me.TextBox1.Value = "" Then UserForm_Initialize: Exit Sub
    With Sheets("data").Cells(1).CurrentRegion
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(3).Address & _
        "=""" & Me.TextBox1 & """,row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then a = Application.Index(.Value, _
        Application.Transpose(x), Evaluate("column(" & .Rows(1).Address & ")"))
    End With
    If Not IsArray(a) Then Exit Sub
    If UBound(x) = 0 Then
        ReDim Preserve a(1 To UBound(a) + 1)
        a(UBound(a)) = Application.Sum(Application.Index(a, Array(6, 7)))
        Dr = a(6): Cr = a(7)
        a(UBound(a)) = Dr - Cr
        Me.ListBox1.Column = a
    Else
        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
        a(1, UBound(a, 2)) = a(1, 6) - a(1, 7)
        Dr = a(1, 6): Cr = a(1, 7)
        For i = 2 To UBound(a, 1)
            a(i, UBound(a, 2)) = a(i - 1, UBound(a, 2)) + a(i, 6) - a(i, 7)
            Dr = Dr + a(i, 6): Cr = Cr + a(i, 7)
        Next
        Me.ListBox1.List = a
    End If
    Me.TextBox2 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Cr, "#,#.00;-#,#.00;0")
    With Me.TextBox4
        .ForeColor = vbBlack
        .Value = Format$(Dr - Cr, "#,#.00;-#,#.00;0")
        If Dr - Cr < 0 Then .ForeColor = vbRed
    End With
End Sub

[MKLAQ]

sorry but it shows another problem . the values in textbox(debit) takes total COL credit . it should take TOTAL COL DEBIT . the same thing credit takes TOTAL COL DEBIT . it should take TOTAL COL CREDIT .this problem happens when run the userform
see the picture, please

[jindon]

Replace Initialize code with

Private Sub UserForm_Initialize()
    Dim a, i As Long, Cr As Double, Dr As Double
    With Sheets("data").Cells(1).CurrentRegion
        a = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)
      For i = 1 To UBound(a, 1)
        a(i, UBound(a, 2)) = a(i, 6) - a(i, 7)
        Dr = Dr + a(i, 6): Cr = Cr + a(i, 7)
    Next
    With Me.ListBox1
        .ColumnCount = UBound(a, 2)
        .List = a
    End With
    Me.TextBox2 = Format$(Dr, "#,#.00;-#,#.00;0")
    Me.TextBox3 = Format$(Cr, "#,#.00;-#,#.00;0")
    With Me.TextBox4
        .ForeColor = vbBlack
        .Value = Format$(Dr - Cr, "#,#.00;-#,#.00;0")
        If Dr - Cr < 0 Then .ForeColor = vbRed
    End With
End Sub

[MKLAQ]

thats great evrey thing is ok. I know it take more time to achieve that and I apprecaite your help
last thing I would show the format number in COL 6,7,8 IN LISTBOX as shows in textbox2,3,4
I try write this and add your code but not succedd like this

finally when write the name in textbox .it should filter based on letter A-Z . it shows the names when I start writing the letters and when I finish the name should show only what I complete writing in textbox.

[MKLAQ]

it seems post# 20 is not clear . any way thanks so much espiacally save your time to write a macro to achieve for this a great work. nobody does that except Few of the members do that like you .
have a good day