Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

sum amount for whole column in listbox based on date

0

Hello 

I added  this  procedure in  the  end of  code in UserForm_Initialize event 

Dim MySum   As Double
     Dim r As Long
        MySum = 0
        With ListBox1
            For r = 0 To .ListCount - 1
            If Format(.List(r, 1), "dd/mm/yyyy") = Date Then
                MySum = MySum + .List(r, 9)
                End If
            Next r
        End With

        TextBox2.Value = MySum

this  procedure  should  sum  the  whole  amounts  for  column 10 in listbox  based on DATE(TODAY)  for column 2 in listbox into textbox2 when populate data in listbox , but  so  far  show  me  zero in textbox2 .

so  what's  the  right  way  to  do  that ?

excel
MS Office Version: 0ffice 2019
posted by: Mussala (rep: 12) on Mon Aug 28, 2023 at 4:02 pm
Answer
Discuss

Answers

0
Selected Answer

Mussala

Your code doesn't work because ListBox1 isn't populated in the Initialize code.

Because I also used the Inititlize code to reset the filtered search (in your previous question), I suggest that your code is removed from Initialize and sits behind  a new CommandButton1- now added with caption "Get today's values" (and shaded green) in the attached file. I've added code (in bold) to populate the other textboxes so that your filter on today's records can be done.

Revision 2, 29 August 2023:

In addtition, the revised file attached will show this month's Sales or Buy records (if chosen, Sales if not) and filter to show just values for today. The associated label are hidden (via Initialize) and only revealed if the "Get Today's value" button is pressed. If ComboBox1 is changed, they're hidden again.

Private Sub CommandButton1_Click()

    ' set for values (defaulting to Sales)
  If Not (ComboBox1.Text = "SALES" Or ComboBox1.Text = "BUY") Then ComboBox1.Text = "SALES"
  ComboBox2.Value = Month(Date)
  Call LBoxPop
  ' filter for today's and sum
  Dim MySum   As Double
     Dim r As Long
        MySum = 0
        With ListBox1
            For r = .ListCount - 1 To 1 Step -1
                ' if it's today's date
                If CDate(.List(r, 1)) = Date Then
                    ' then add to sum
                    MySum = MySum + .List(r, 9)
                    Else
                    ' else remove from list
                    .RemoveItem r
                End If
            Next r
        End With

    TextBox2.Value = Format(MySum, "#,##0.00")
    ' reveal textbox2 and label
    TextBox2.Visible = True
    Label5.Visible = True
End Sub

Note that rather than compare strings, it now converts the cell value to a date for comparison with Date and also loops backwards removing items which aren't today. The attached file now includes a item with today's date (shaded yellow) in both Sales abd Buy tabs.

Just click that button on the UserForm in the attached revised file.

Hope this helps.

posted by: John_Ru (rep: 6152) on Tue Aug 29, 2023 at 3:45 am
Discuss

Discussion

Hi John,
still show zero in textbox2 based on your suggestion and attachment !
Mussala (rep: 12) Aug 29, '23 at 3:56 am
Oops! See revised Answer and new file
John_Ru (rep: 6152) Aug 29, '23 at 4:08 am
perfect !
many  thanks for help
Mussala (rep: 12) Aug 29, '23 at 4:25 am
Thanks for selecting my Answer, Mussala.

Personally, I'd change the Label "AMOUNT" to read "Today's Sales" and make that and the associated TextBox visible only after the Get Todays Sales button is clicked.
John_Ru (rep: 6152) Aug 29, '23 at 4:32 am
well, you seem to  forget BUY sheet .
I suggest use IF  to  achive that to become like  this 
If ComboBox1.Text = "SALES" Or ComboBox1.Text = "BUY" Then
  Dim MySum   As Double
     Dim r As Long
        MySum = 0
        With ListBox1
            For r = 0 To .ListCount - 1
            If Format(.List(r, 1), "dd/mm/yyyy") = CStr(Date) Then
                MySum = MySum + .List(r, 9)
                End If
            Next r
        End With
 
        TextBox2.Value = MySum
        End If
End Sub

and  that  works when select any sheet from combobox1 .
Mussala (rep: 12) Aug 29, '23 at 4:43 am
Okay, that's your choice. I just wanted to illustare why your Inialize code failed and a way to solve it.

I think it's better if you convert the cell value for comparison with Date and loop backwards removing items which aren't today, so (changes in bold):

        With ListBox1
            For r = .ListCount - 1 To 1 Step -1
                ' if it's today's date
                If CDate(.List(r, 1)) = Date Then
                    ' then add to sum
                    MySum = MySum + .List(r, 9)
                    Else
                    ' else remove from list
                    .RemoveItem r
                End If
            Next r
        End With


Let me know if you agree and I'll modify my Answer.
John_Ru (rep: 6152) Aug 29, '23 at 5:33 am
yes I gree with you , really better.
Mussala (rep: 12) Aug 29, '23 at 6:16 am
Done that (including ComboBox1 choice or default of Sales) and added minor improvements- today values only show after the button is pressed and the value is now formatted. Some minor changes to other procedures to do that (Initialize and ComboBox1_Change)
John_Ru (rep: 6152) Aug 29, '23 at 7:51 am
thanks again .
Mussala (rep: 12) Aug 29, '23 at 8:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login