Insert column in listbox & populate values in textboxes

[tubrak]

hi
I need from experts to help me to complete my project . currently when I search the customer it just show the debit , credit what I want when run userform show all of data and populate values after summing total of column DEBIT, column CREDIT and populate the values in textbox(DEBIT,CREDIT) and textbox (BALANCE) should subtract between textbox(DEBIT,CREDIT) with shows number format as in pic1 and when search specific customer should insert column BALANCE in listbox and calculate the values . in first row BALANCE = DEBIT-CREDIT the second BALANCE= provoius BALANCE + DEBIT-CREDIT with shows number formatting in list box as in PIC2
this is the whole code

Private Sub UserForm_Initialize()
   Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row   
    With ListBox1
        .ColumnCount = 6   
        .ColumnWidths = "50;100;50;70;70;50;50"   
        .List = Range("A1:F" & LastRow).Value   
    End With
End Sub

Private Sub MY_List_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyEscape Then
 Me.MY_Text = ""
 Me.MY_Text.SetFocus
ElseIf KeyCode = vbKeyF12 Then
 Unload Me
End If
End Sub
Private Sub CommandButton4_Click()
Dim MyValue
Dim MyAr() As String
Dim ib As Boolean
Dim RR As Integer, I As Integer, ii As Integer
Dim RRr As Integer, LR As Integer
Dim dt1 As Date, dt2 As Date
Dim LastRow As Integer, R As Integer, T As Integer, s As Integer
MY_List.Clear
With ActiveSheet
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    For R = 2 To LastRow
        If .Cells(R, "C") Like "*" & MY_Text.Text & "*" Then
            MY_List.AddItem
            MY_List.List(T, 0) = .Cells(R, "A")
            MY_List.List(T, 1) = Format(.Cells(R, "B"), "yyyy/mm/dd")
            MY_List.List(T, 2) = .Cells(R, "C")
            MY_List.List(T, 3) = .Cells(R, "D")
            MY_List.List(T, 4) = .Cells(R, "E")
            MY_List.List(T, 5) = .Cells(R, "F")
            T = T + 1
         End If
    Next
End With

I hope some body help

 

[tubrak]

I hope some body help

 

[DanteAmor]

How about.

Change the following data in the code for the names of your controls.
Deb_txt.Value = Format(dbt, "#,##0.00")
Cre_txt.Value = Format(cdt, "#,##0.00")
Bal_txt.Value = Format(blc, "#,##0.00")

Replace all your code with the following:

Private Sub UserForm_Initialize()
  Dim LastRow As Long
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  With MY_List
    .ColumnCount = 7
    .ColumnWidths = "20;50;100;100;70;70;70"
    Call CommandButton4_Click
  End With
End Sub

Private Sub MY_List_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = vbKeyEscape Then
    Me.MY_Text = ""
    Me.MY_Text.SetFocus
  ElseIf KeyCode = vbKeyF12 Then
    Unload Me
  End If
End Sub

Private Sub CommandButton4_Click()
  Dim sh As Worksheet
  Dim r As Integer, t As Integer
  Dim dbt As Double, cdt As Double, blc As Double
  
  Set sh = ActiveSheet
  With MY_List
    .Clear
    For r = 1 To sh.Cells(Rows.Count, "C").End(xlUp).Row
      If LCase(sh.Cells(r, "C")) Like "*" & LCase(MY_Text.Text) & "*" Or sh.Cells(r, "C") = "Name" Then
        .AddItem
        .List(t, 0) = sh.Cells(r, "A")
        .List(t, 1) = Format(sh.Cells(r, "B"), "yyyy/mm/dd")
        .List(t, 2) = sh.Cells(r, "C")
        .List(t, 3) = sh.Cells(r, "D")
        .List(t, 4) = Format(sh.Cells(r, "E"), "#,##0.00")
        .List(t, 5) = Format(sh.Cells(r, "F"), "#,##0.00")
        If r = 1 Then
          If MY_Text.Value <> "" Then .List(t, 6) = "BALANCE"
        Else
          dbt = dbt + sh.Cells(r, "E")
          cdt = cdt + sh.Cells(r, "F")
          blc = blc + sh.Cells(r, "E") - sh.Cells(r, "F")
          If MY_Text.Value <> "" Then .List(t, 6) = Format(blc, "#,##0.00")
        End If
        t = t + 1
      End If
    Next
    Deb_txt.Value = Format(dbt, "#,##0.00")
    Cre_txt.Value = Format(cdt, "#,##0.00")
    Bal_txt.Value = Format(blc, "#,##0.00")
  End With
End Sub

 

[tubrak]

astonishing ! thanks so much for a great help

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.