abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,486
- Office Version
- 2019
- 2010
- Platform
- Windows
Hello
I want calculation in listbox for BALANCE column when search the id in second column in listbox into textbox 4 then should change the calculation .
first I select the sheet name from combobox1 and will populate data in listbox and will populate the last row from sheet into textbox1,2,3
now when search for ID in second column for last part as in pic2 then should calculate like this
the first row in listbox for column (BALANCE)= 0-2200=-2200
second row in listbox for column (BALANCE)=-2200+(-4200)=-6400
third row in listbox for column (BALANCE)= -6400+7000-6200=57400
and should change the lastrow(TOT) in listbox should sum the INPUT column ,OUTPUT column and BALANCE should subtract INPUT from OUTPUT(70000-12600=57400)
first when select sheet from combobox1
result when search in textbox4
I hope this is possible.
I want calculation in listbox for BALANCE column when search the id in second column in listbox into textbox 4 then should change the calculation .
first I select the sheet name from combobox1 and will populate data in listbox and will populate the last row from sheet into textbox1,2,3
now when search for ID in second column for last part as in pic2 then should calculate like this
the first row in listbox for column (BALANCE)= 0-2200=-2200
second row in listbox for column (BALANCE)=-2200+(-4200)=-6400
third row in listbox for column (BALANCE)= -6400+7000-6200=57400
and should change the lastrow(TOT) in listbox should sum the INPUT column ,OUTPUT column and BALANCE should subtract INPUT from OUTPUT(70000-12600=57400)
first when select sheet from combobox1
result when search in textbox4
VBA Code:
Option Explicit
Option Compare Text
Private Data, Temp, Crit As String, i As Long, lr As Long, ii As Long, x As Long
Dim ws As Worksheet
Private Sub ComboBox1_Change()
'ActiveSheet.Visible = True
If ComboBox1.Value <> "" Then
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
CommandButton1.Enabled = True
End If
If ComboBox1.Value = "" Then ListBox1.Clear
If ComboBox1.Value = "" Then TextBox1.Value = "": TextBox2.Value = "": TextBox3.Value = ""
If ComboBox1.Value <> "" Then
TextBox1.Visible = True: TextBox2.Visible = True: TextBox3.Visible = True
If ComboBox1.Value = "" Then Exit Sub
Set ws = Sheets(ComboBox1.Value)
ws.Activate
With ws
lr = .Range("A" & Rows.Count).End(xlUp).Row
TextBox1.Value = .Range("C" & lr).Value
TextBox2.Value = .Range("D" & lr).Value
TextBox3.Value = .Range("E" & lr).Value
If TextBox3.Value < 0 Then
TextBox3.ForeColor = vbRed
Else: TextBox3.ForeColor = vbBlack
End If
End With
Call LBoxPop
End If
End Sub
Private Sub TextBox1_Change()
TextBox1.Value = Format(TextBox1, "#,##0.00")
Call LBoxPop
End Sub
Private Sub TextBox2_Change()
TextBox2.Value = Format(TextBox2, "#,##0.00")
End Sub
Private Sub TextBox3_Change()
TextBox3.Value = Format(TextBox3, "#,##0.00")
End Sub
Private Sub UserForm_Initialize()
Crit = ""
Dim i As Long
For i = 1 To Sheets.Count
If Sheets(i).Name <> "DATA" And Sheets(i).Name <> "MAIN" Then
ComboBox1.AddItem Sheets(i).Name
End If
Next
If ComboBox1.ListIndex > -1 Then
Set ws = Sheets(ComboBox1.Value)
Call LBoxPop
End If
If ComboBox1.Value = "" Or ComboBox1.Value <> "" Then CommandButton1.Enabled = False
ListBox1.SetFocus
End Sub
Private Sub LBoxPop()
Dim r As Long, c As Long
Dim Data() As Variant
Dim rng As Range
Dim va
Debug.Print ws.Name
Set rng = ws.Cells(1, 1).CurrentRegion
'
Data = ws.Cells(1, 1).CurrentRegion.Value
For i = 1 To UBound(Data, 1)
Data(i, 1) = Format(Data(i, 1), "yyyy-mm-dd")
Next
For i = 1 To UBound(Data, 1)
Data(i, 3) = Format(Data(i, 3), "#,##0.00")
Data(i, 4) = Format(Data(i, 4), "#,##0.00")
Data(i, 5) = Format(Data(i, 5), "#,##0.00")
Next
With UserForm1.ListBox1
.ColumnCount = 5
.columnWidths = "90;300;120;120;100"
.List = Data
End With
For i = ListBox1.ListCount - 1 To 0 Step -1
Debug.Print i, ListBox1.List(i, 0)
If ListBox1.List(i, 0) <> "" Then
ListBox1.ListIndex = i
Exit For
End If
Next i
End Sub