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

Abdo

Board Regular
Joined
May 16, 2022
Messages
225
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
FIRST STEP.PNG


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
SECOND STEP.PNG


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

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
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Create a new sheet and put the following:
Cell Formulas
RangeFormula
B4:K4B4=DATE(YEAR($G$2)+COLUMNS($B3:B3),MONTH($G$2),DAY($G$2))
B5B5=$B$2
C5:K5C5=B5+$F$2
B6:K6B6=$F$2
B7:K7B7=B5+B6


Code in userform:

VBA Code:
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

😅
 
Upvote 0
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 !(y)
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
1.PNG



and what I want based on OP
THIRD STEP.PNG
 
Upvote 0
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:

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

;)
 
Upvote 0
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.
VBA Code:
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

😇
 
Upvote 0
Solution
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
 
Upvote 0
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.

🧙‍♂️
 
Upvote 0
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
VBA Code:
' 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.;)
 
Upvote 1

Forum statistics

Threads
1,224,226
Messages
6,177,274
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top