VBA ListBox display information by product between two dates

omphe

New Member
Joined
Aug 16, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Good Day Everyone;

I have a sheet named Products, the sheet has three columns namely Date (column A), Product Name (column B) , Amount (column C).
I created a VBA form with a Listbox, a combo box (with all product names), two text boxes to insert a start date, and an end date.
The text boxes are linked to a date picker button that selects a date when clicked.

I want to be able to show the product names by the start and end dates on the list box.
So when a product name is selected on the combo box, and two dates are present on the text boxes, the list box should display all three columns in the sheet Products by product name for those two dates.
I have noted the code below, however it does not seem to work.
Kindly please assist with the proper code.
Note, I want to retain the column headers on the form, so I already have another sub-procecure of the from when activated it sets the column headers to True, and count of columns to 3.

VBA Code:
Private Sub cmd_submit_Click()
Dim i As Long
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Products")

Me.ListBox1.Clear

For i = 2 To Application.WorksheetFunction.CountA(sh.Range("A:A"))
If sh.Cells(i, "B").Value = Me.cbx_name.Value And sh.Cells(i, 1).Value >= _
CDate(Me.txt_start.Text) And sh.Cells(i, 1).Value <= _
CDate(Me.txt_end.Text) Then
 
Me.ListBox1.AddItem sh.Cells(i, "A").Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = sh.Cells(i, "B").Value
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = sh.Cells(i, "C").Value


End If
Next i
 
End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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