I have code got by Dante amore's assistance .
so the code will populate the whole data in listbox and when I fill name in textbox name (MY_Text) will add column in listbox to calculate the balance
by summing and subtracting amongst the rows in listbox between columns E,F and will calculate into textboxes names( Deb_txt, Cre_txt) by summing based on columns E,F and textbox name (Bal_txt.Value) =Deb_txt-Cre_txt
now I want adding two textboxs names (textbox1,textbox2) to search for date from and to
whether I write the name or not inMY_Text
if I write name and fill two dates then will show data and calculate within two dates , if I don't write name but write two dates then will populate based on two dates .
this is the orginal thread to understand more .
Insert column in listbox & populate values in textboxes
first when write name will populate data and calculate the balance
second when write two dates with names
third when just search two dates
the wholes codes
I hope somebody can modify.
so the code will populate the whole data in listbox and when I fill name in textbox name (MY_Text) will add column in listbox to calculate the balance
by summing and subtracting amongst the rows in listbox between columns E,F and will calculate into textboxes names( Deb_txt, Cre_txt) by summing based on columns E,F and textbox name (Bal_txt.Value) =Deb_txt-Cre_txt
now I want adding two textboxs names (textbox1,textbox2) to search for date from and to
whether I write the name or not inMY_Text
if I write name and fill two dates then will show data and calculate within two dates , if I don't write name but write two dates then will populate based on two dates .
this is the orginal thread to understand more .
Insert column in listbox & populate values in textboxes
first when write name will populate data and calculate the balance
second when write two dates with names
third when just search two dates
the wholes codes
VBA Code:
'dante
'tubrak
'https://www.mrexcel.com/board/threads/insert-column-in-listbox-populate-values-in-textboxes.1183845/
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
Private Sub MY_Text_Change()
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
Private Sub UserForm_Initialize()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
With MY_List
.ColumnCount = 7
.ColumnWidths = "50;80,80;80;80"
Call CommandButton1_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
I hope somebody can modify.