take amount from last row in listbox on form and put into next column

[Abdo]

Hi,
I need populate data in listbox based on two conditions in combobox1 and depends on filled textboxes.
the original data when I fill in textboxes and combobox1 is empty

so based on filled date in textbox6=09/10/2023 and textbox3=5 and combobox1 is empty will should do:
1- in first column should populate names as show ( it always should show four items).
2- based on textbox6=09/10/2023 should calculate to reach year (meaning should be 09/10/2024 as show in second column ) , the next column show next year ,should calculate one year depends on previous column contains date in row1.
3- the maximum years should be 5 years as show until 09/10/2028 depends on textbox3= 5
4- as mounts for adjacent item in first column and under date for each year should copy from textbox1,textbox5(brings amount from textbox1 will just for second column after that next column will depend on previous column for last amount in last row ) , last item NET COST OF MACHINES = sum amounts for COST OF MACHINES , DEPRECIATION VALUE for each date .
6- the next year will brings amount from last amount in previous year
example : in third column will take 8400 from second column in last row . so on every next year will take amount from last amount is existed in last row for previous column .

the result should be

if I select YEARLY from combobox1.
then just apply based on current yearly .
result

so in this case will show for 2024 after I leave 2024 and enter 2025 and when reach for 09/10/2025 then should show for year 2024,2025
and so on for entering new year, then should keep old years when add new year .
my date format will be DD/MM/YYYY
thanks

 

[DanteAmor]

Create a new sheet and put the following:

varios 09oct2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	CALC	Cost	Salvage	Life	Rate	Depr	Date
2		10,000.00	2,000.00	5.00	20.00	-1,600.00	09/10/2023
3
4	YEAR	09/10/2024	09/10/2025	09/10/2026	09/10/2027	09/10/2028	09/10/2029	09/10/2030	09/10/2031	09/10/2032	09/10/2033
5	Cost Of Machines	10,000.00	8,400.00	6,800.00	5,200.00	3,600.00	2,000.00	400.00	-1,200.00	-2,800.00	-4,400.00
6	Depreciation Value	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00	-1,600.00
7	Net Cost Of Machines	8,400.00	6,800.00	5,200.00	3,600.00	2,000.00	400.00	-1,200.00	-2,800.00	-4,400.00	-6,000.00
CALC

Cell Formulas
Range	Formula
B4:K4	B4	=DATE(YEAR($G$2)+COLUMNS($B3:B3),MONTH($G$2),DAY($G$2))
B5	B5	=$B$2
C5:K5	C5	=B5+$F$2
B6:K6	B6	=$F$2
B7:K7	B7	=B5+B6

Code in userform:

Private Sub CommandButton1_Click()
  Dim vLife As Double
  Dim i As Long, j As Long
  Dim sh As Worksheet
  Dim b As Variant
  
  Set sh = Sheets("CALC")       'Fit the sheet name
  
  'VALIDATIONS
  If validations = False Then Exit Sub
  
  'FILL SHEET
  vLife = GetVle(TextBox3.Value)
  sh.Range("B2").Value = GetVle(TextBox1.Value)
  sh.Range("C2").Value = GetVle(TextBox2.Value)
  sh.Range("D2").Value = vLife
  sh.Range("E2").Value = GetVle(TextBox4.Value)
  sh.Range("F2").Value = GetVle(TextBox5.Value)
  sh.Range("G2").Value = CDate(TextBox6.Value)
    
  'FILL LISTBOX
  b = sh.Range("A4").Resize(4, vLife + 1).Value
  For i = 2 To UBound(b, 1)
    For j = 2 To UBound(b, 2)
      b(i, j) = "LYD " & Format(b(i, j), "#,##0.00;-#,##0.00")
    Next
  Next

  With ListBox1
    If ComboBox1.Value = "" Then
      .ColumnCount = vLife + 1
    ElseIf ComboBox1.Value = "YEARLY" Then
      .ColumnCount = 2
    End If
    .List = b
  End With
End Sub

Function GetVle(txt As String)
  GetVle = Val(Trim(Replace(Replace(Replace(txt, "LYD", ""), "%", ""), ",", "")))
End Function

Function validations()
  Dim i As Long
  Dim vle As String
  
  validations = True
  For i = 1 To 6
    If Controls("TextBox" & i).Value = "" Then
      MsgBox "Enter data in TextBox" & i
      Controls("TextBox" & i).SetFocus
      validations = False
      Exit Function
    End If
    
    Select Case i
      Case 1 To 5
        vle = Controls("TextBox" & i).Value
        vle = Trim(Replace(Replace(Replace(vle, "LYD", ""), "%", ""), ",", ""))
        If Not IsNumeric(vle) Then
          MsgBox "Invalid data in TextBox" & i
          Controls("TextBox" & i).SetFocus
          validations = False
          Exit Function
        End If
      Case 6
        If Not IsDate(Controls("TextBox" & i).Value) Then
          MsgBox "Invalid date in TextBox" & i
          Controls("TextBox" & i).SetFocus
          validations = False
          Exit Function
        End If
    End Select
  Next
End Function

 

[Abdo]

thanks for your solution .
well I don't prefer using formulas inside sheet , I expect doing directly on form without using sheet. I know this way is complicated and not to easy to do that .
if you see this the best way I don't mind .
I tested the code and works excellently !
I need understand using about 10 years inside the sheet , is it maximum years when I write number in textbox3 or...?
also I would fix when select yearly from combobox1 and show 2024 year it will leave column 2,3 are empty I suppose starting from column 2 when show data in list box .
this is what I got

and what I want based on OP

 

[DanteAmor]

well I don't prefer using formulas inside sheet

It can be any sheet, it can even be hidden.

I need understand using about 10 years inside the sheet

You can use 10 or 20 or more, just copy the formulas to the right.
The code will react to the number you put in textbox 3

it will leave column 2,3 are empty

Columns 2 and 3 are not empty, the space you see is because column 1 and 2 became larger, just adjust the width of columns 1 and 2 in the listbox, only add this line in Initialize event, for example:

Private Sub UserForm_Initialize()
  ListBox1.ColumnWidths = "100;80"
End Sub

 

[DanteAmor]

I tested the code and works excellently !

I expect doing directly on form without using sheet

Here is the updated code without using sheet, it is even shorter and improved.

Private Sub CommandButton1_Click()
  Dim vCost As Double, vSalv As Double, vLife As Double
  Dim vRate As Double, vDepr As Double, vDate As Double
  Dim j As Long, depValue As Double
  Dim b As Variant
 
  ListBox1.Clear
  If validations = False Then Exit Sub
 
  vCost = GetVle(TextBox1.Value)
  vSalv = GetVle(TextBox2.Value)
  vLife = GetVle(TextBox3.Value)
  vRate = GetVle(TextBox4.Value)
  vDepr = GetVle(TextBox5.Value)
  vDate = CDate(TextBox6.Value)

  ReDim b(1 To 4, 1 To vLife + 1)
  b(1, 1) = "Year"
  b(2, 1) = "Cost Of Machines"
  b(3, 1) = "Depreciation Value"
  b(4, 1) = "Net Cost Of Machines"

  For j = 1 To vLife
    vDate = DateSerial(Year(vDate) + 1, Month(vDate), Day(vDate))
    b(1, j + 1) = Format(vDate, "dd/mm/yyyy")
    vCost = vCost + depValue
    b(2, j + 1) = "LYD " & Format(vCost, "#,##0.00;-#,##0.00;")
    b(3, j + 1) = "LYD " & Format(vDepr, "#,##0.00;-#,##0.00;")
    b(4, j + 1) = "LYD " & Format(vCost + vDepr, "#,##0.00;-#,##0.00;")
    depValue = vDepr
  Next

  With ListBox1
    .ColumnWidths = "100;80;80;80;80;80;80;80;80;80;80;80;80"
    If ComboBox1.Value = "" Then .ColumnCount = vLife + 1
    If ComboBox1.Value = "YEARLY" Then .ColumnCount = 2
    .List = b
  End With
End Sub

Function GetVle(txt As String)
  GetVle = Trim(Replace(Replace(Replace(txt, "LYD", ""), "%", ""), ",", ""))
End Function

Function validations()
  Dim i As Long
  Dim vle As String, cad As String
 
  For i = 1 To 6
    If Controls("TextBox" & i).Value = "" Then cad = cad & "Enter data in TextBox" & i & vbCr
    Select Case i
      Case 1 To 5
        vle = GetVle(Controls("TextBox" & i).Value)
        If Not IsNumeric(vle) Then cad = cad & "Invalid data in TextBox" & i & vbCr
      Case 6
        If Not IsDate(Controls("TextBox" & i).Value) Then cad = cad & "Invalid date in TextBox" & i & vbCr
    End Select
  Next
 
  If cad <> "" Then MsgBox cad
  validations = cad = ""
End Function

 

[Abdo]

Here is the updated code without using sheet, it is even shorter and improved.

awesome!

if I select YEARLY from combobox1.

so in this case will show for 2024 after I leave 2024 and enter 2025 and when reach for 09/10/2025 then should show for year 2024,2025
and so on for entering new year, then should keep old years when add new year .

I'm afraid this case doesn't work well.
example : if I textbox6=09/10/2022 and if I select yearly from combobox 1 then will show 09/10/2023, 09/10/2024 , but the code just show current year 2024.
should show before current year as long locate within number in textbox3

 

[DanteAmor]

so in this case will show for 2024 after I leave 2024 and enter 2025 and when reach for 09/10/2025 then should show for year 2024,2025
and so on for entering new year, then should keep old years when add new year .

You could explain that sequence with screens.
I don't understand where you are going to put "enter 2025" nor do I understand where you are going to put "add new year"
It is very confusing, you put "10/09/2023" and it must start with "10/09/2024", then you want "10/09/2024" and "10/09/2025" to appear without putting 2024, of the 2023 you jumped to 2025.
And if instead of putting "YEARLY", you simply put in the textbox3 = 1, then 2, then 3, etc.

If you change the values, can't you keep the 2024 values, then put new 2025 values, 2024 goes with the old values and 2025 will be calculated with the new values?

I repeat, it is very confusing and you only gave an example.

EDIT:
Consider that the execution type "" or "YERALY" is not being stored, so if you execute with "" and then with "YEARLY" , how do you know if you have to start or accumulate?

It's not that simple.

 

[Abdo]

I repeat, it is very confusing and you only gave an example.

My apologies!

And if instead of putting "YEARLY", you simply put in the textbox3 = 1, then 2, then 3, etc.

I supposes Cancelling combobox1 by deletion totally, so no need theses lines

' If ComboBox1.Value = "" Then .ColumnCount = vLife + 1
    'If ComboBox1.Value = "YEARLY" Then .ColumnCount = 2

in this case your suggestion works perfectly without combobox1 is existed.
many thank for your help.