five cases to sort data into listbox on userform based on date

Mussa

Board Regular
Joined
Jul 12, 2021
Messages
225
Office Version
  1. 2019
  2. 2010
hi experts
I need mod this code or alternitive. so I attach some pictures for five cases. the code populates data based on column (4) in listbox. now I would make it more flexible to sort based on date for item is written in textbox1 .
first this is data in sheet "PURCHASE"
FILTER.xlsm
ABCDEFGHIJ
1DATE CLIENT NOINVOICE NOID-CCBB-RTT-YOR-GGQTYPRICETOTAL
21/5/2021CC2-TRB-1INV-TRGG-1IDTR-100FFDOOD-1PACK-2TTR60.00$120.00$7,200.00
31/6/2021CC2-TRB-1INV-TRGG-1IDTR-101FFDOOD-2PACK-3BBR10.00$130.00$1,300.00
41/7/2021CC2-TRB-1INV-TRGG-1IDTR-102FFDOOD-3PACK-4LLR5.00$100.00$500.00
51/8/2021CC2-TRB-1INV-TRGG-1IDTR-103FFDOOD-4PACK-5MMR10.00$289.00$2,890.00
61/9/2021CC2-TRB-2INV-TRGG-2IDTR-104FFDOOD-5PACK-6ASDR5.00$140.00$700.00
71/10/2021CC2-TRB-2INV-TRGG-2IDTR-100FFDOOD-1PACK-2TTR30.00$100.00$3,000.00
81/11/2021CC2-TRB-2INV-TRGG-2IDTR-101FFDOOD-2PACK-3BBR50.00$120.00$6,000.00
91/12/2021CC2-TRB-2INV-TRGG-2IDTR-107FFDOOD-6PACK-7FFG12.00$200.00$2,400.00
101/13/2021CC2-TRB-2INV-TRGG-2IDTR-108FFDOOD-7PACK-8GGF13.00$100.00$1,300.00
PURCHASE
Cell Formulas
RangeFormula
J2:J10J2=H2*I2


and this show when run the userform
1.PNG



case 1 I want sort data based on select last date after write item in textbox1 based on column 4 in listbox
2.PNG



case 2 I want sort data based on select old date after write item in textbox1 based on column 4 in listbox
3.PNG


case 3 I want sort data based on select reset after write item in textbox1 based on column 4 in listbox should show whether last or old date together
4.PNG




case 4 I want sort data based on select month and combobox for specific month after write item in textbox1 based on column 4 in listbox
note: I've found to be cancel optionbutton MONTH and just depends on combobox1 to select month
5.PNG


case 5 I want sort data based on write the date (from,to) in textbox2,3 after write item in textbox1 based on column 4 in listbox
6.PNG



and this is the whole code
VBA Code:
Option Explicit

Dim a As Variant

Private Sub TextBox1_Change()
  Call FilterData
End Sub

Private Sub TextBox2_Change()
  Call FilterData
End Sub

Private Sub TextBox3_Change()
  Call FilterData
End Sub

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim I As Long, j As Long, k As Long
 
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For I = 1 To UBound(a)
    If TextBox1 = "" Then txt1 = a(I, 4) Else txt1 = TextBox1
 
    If LCase(a(I, 4)) Like LCase(txt1) & "*" Then
      k = k + 1
      For j = 1 To 10
        If j = 1 Then 'Change the 1 in this line for the column number where you have the date
          b(k, j) = Format(a(I, j), "dd/mm/yyyy")
        Else
          b(k, j) = a(I, j)
        End If
      Next
    End If
  Next
  If k > 0 Then ListBox1.List = b
End Sub


Private Sub UserForm_Initialize()
    Dim lindex&
    Dim rngDB As Range, rng As Range
    Dim I, myFormat(1) As String
    Dim sWidth As String
    Dim vR() As Variant
    Dim n As Integer
    Dim myMax As Single
    Set rngDB = Range("A2:J20")
    For Each rng In rngDB
        n = n + 1
        ReDim Preserve vR(1 To n)
        vR(n) = rng.EntireColumn.Width
    Next rng
    myMax = WorksheetFunction.Max(vR)
    For I = 1 To n
        vR(I) = myMax
    Next I
    With Sheets("purchase").Cells(1).CurrentRegion
        myFormat(0) = .Cells(2, 8).NumberFormatLocal
        myFormat(1) = .Cells(2, 9).NumberFormatLocal
        Set rng = .Offset(1).Resize(.Rows.Count - 1)
        a = .Cells(1).CurrentRegion.Value
    End With

    sWidth = Join(vR, ";")
    Debug.Print sWidth
    With ListBox1
        .ColumnCount = 10
        .ColumnWidths = sWidth '<~~ 63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63;63
        .List = rng.Value
        .BorderStyle = fmBorderStyleSingle
        For lindex = 0 To .ListCount - 1
            .List(lindex, 0) = (Format((.List(lindex, 0)), "dd/mm/yyyy"))   ' BL = dates
            .List(lindex, 7) = Format$(.List(lindex, 7), myFormat(0))
            .List(lindex, 8) = Format$(.List(lindex, 8), myFormat(1))
            .List(lindex, 9) = Format$(.List(lindex, 9), myFormat(1))
        Next
    End With
End Sub
any help would truly appreciate.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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