VBA Code for displaying monthly sales in LISTBOX.

[bryskie]

help pls. i cant move on my project.
kept on encountering this error, " Unable to get the EDate property of the WorksheetFunction class"

Here's the code.

Private Sub UserForm_Initialize()

Me.COMBOBOXYear.Value = Format(Date, "YYYY")
Me.COMBOBOXMonth.Value = Format(Date, "MMMM")
For a = 0 To 5
Me.COMBOBOXYear.AddItem Format(Date, "YYYY") - a
Next a
For b = 0 To 11
c = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.COMBOBOXYear.Value, b)
Me.COMBOBOXMonth.AddItem Format(c, "MMMM")
Next b

End Sub

Private Sub COMBOBOXYear_Change()


Me.LBOXSales.Clear
Me.LBOXSales.AddItem Sheet10.Cells(1, 1).Value
For a = 1 To 4
Me.LBOXSales.List(LBOXSales.ListCount - 1, a) = Sheet10.Cells(1, a + 1).Value
Next a
Me.LBOXSales.Selected(0) = True
For i = 2 To Application.WorksheetFunction.CountA(Sheet10.Range("A:A"))
b = Application.WorksheetFunction.EDate("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
c = Application.WorksheetFunction.EoMonth("1" & "/" & Me.COMBOBOXMonth.Value & "/" & Me.COMBOBOXYear, 0)
If Sheet10.Cells(i, 1).Value >= CDate(b) And Sheet10.Cells(i, 1).Value <= CDate(c) Then
Me.LBOXSales.AddItem Sheet10.Cells(i, 2).Value
For d = 1 To 4
Me.LBOXSales.List(LBOXSales.ListCount - 1, d) = Sheet10.Cells(i, d + 1).Value
Next d
End If
Next i

End Sub

Thank you!

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

I'm sorry, but I do not understand how your data is or how your controls are. You could upload a file with your userform. On a sheet you explain step by step what you want to do.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

here is the link. hope it help in solving. https://drive.google.com/file/d/1MJyM-EaIEA27j4tyItHU4eVgC-raThOn/view?usp=sharing

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

here is the link. hope it help in solving. https://drive.google.com/file/d/1MJyM-EaIEA27j4tyItHU4eVgC-raThOn/view?usp=sharing

Can you explain what you need to do?
Forget the code, what do you want to do in the userform?

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Ok, I understood what you need.
Change your code for the following:

Dim loading As Boolean


Private Sub ComboBox1_Change()
    Call LoadList
End Sub
Private Sub ComboBox2_Change()
    Call LoadList
End Sub


Sub LoadList()
    If loading Then Exit Sub
    ListBox1.Clear
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Format(Cells(i, "A").Value, "yyyymmmm") = ComboBox1.Value & ComboBox2.Value Then
            ListBox1.AddItem Cells(i, "A").Value
            ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(i, "B").Value
            ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(i, "C").Value
            ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(i, "D").Value
        End If
    Next
End Sub


Private Sub UserForm_Initialize()
    loading = True
    Me.ComboBox1.Value = Format(Date, "YYYY")
    Me.ComboBox2.Value = Format(Date, "MMMM")
    For a = 0 To 5
        Me.ComboBox1.AddItem Format(Date, "YYYY") - a
    Next a
    For b = 0 To 11
        c = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.ComboBox1.Value, b)
        Me.ComboBox2.AddItem Format(c, "MMMM")
    Next b
    loading = False
End Sub

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Ok, I understood what you need.
Change your code for the following:

Dim loading As Boolean


Private Sub ComboBox1_Change()
    Call LoadList
End Sub
Private Sub ComboBox2_Change()
    Call LoadList
End Sub


Sub LoadList()
    If loading Then Exit Sub
    ListBox1.Clear
    For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
        If Format(Cells(i, "A").Value, "yyyymmmm") = ComboBox1.Value & ComboBox2.Value Then
            ListBox1.AddItem Cells(i, "A").Value
            ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(i, "B").Value
            ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(i, "C").Value
            ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(i, "D").Value
        End If
    Next
End Sub


Private Sub UserForm_Initialize()
    loading = True
    Me.ComboBox1.Value = Format(Date, "YYYY")
    Me.ComboBox2.Value = Format(Date, "MMMM")
    For a = 0 To 5
        Me.ComboBox1.AddItem Format(Date, "YYYY") - a
    Next a
    For b = 0 To 11
        c = Application.WorksheetFunction.EoMonth("1" & "/" & "January" & "/" & Me.ComboBox1.Value, b)
        Me.ComboBox2.AddItem Format(c, "MMMM")
    Next b
    loading = False
End Sub

Good Day sir

So Close... all i want is ,
for example i chose june 2019, all the data under the June 2019 will display in the list box.

Thank you sir

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Good Day sir

So Close... all i want is ,
for example i chose june 2019, all the data under the June 2019 will display in the list box.

Thank you sir

Just that makes the code

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

Just that makes the code

\

in your code sir. when i chose july it doesnt display all the value under july.
its display only 1 value and under same month june whether i chose june or july.

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

sir dante. now i get it. upon reviewing i saw that my data in excel are the wrong one.
now its fully functional.
Thank you very much. so much appreciated sir.
if you dont mind can i add you so in the future i can ask for help again? if you dont mind. ill private message you.

again sir thank you very much

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

sir dante. now i get it. upon reviewing i saw that my data in excel are the wrong one.
now its fully functional.
Thank you very much. so much appreciated sir.
if you dont mind can i add you so in the future i can ask for help again? if you dont mind. ill private message you.

again sir thank you very much

I'm glad to help you.

You can create a thread and notify me by private message and if I have the opportunity I will gladly review it; although no doubt someone else could also help you, there is an impressive talent in this forum.

 

[bryskie]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

I'm glad to help you.

You can create a thread and notify me by private message and if I have the opportunity I will gladly review it; although no doubt someone else could also help you, there is an impressive talent in this forum.

yes sir. will do. hope to find other friends here. thanks again sir. and Godbless.

 

[DanteAmor]

Re: HELP! VBA Code for displaying monthly sales in LISTBOX.

yes sir. will do. hope to find other friends here. thanks again sir. and Godbless.

I'm glad to help you. I appreciate your kind comments.