Hi I have a UserForm that has a filter Combobox that filters different criteria. and that includes "Year" and a TextBox which will be the search box. and a listbox that will show result
Note: "Year" is in separate column ("T")
the search box is working fine for other criteria, except for "Year" which is encoded only by Numeric (Ex. 2022)
for example if I filter it by "Year" then search for "2022"
It will not show any result even do there is a row/data that contains 2022 under Year column
but, if I edit the data and add a alphabet together with numeric ex "2022A"
and then filter it again by year and search. it will show the data .
for short it does not recognize numeric only in data unless it has an alphabet.
his is how i populate my combobox
Sub Refresh_DropDown_List()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("List")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data")
his is how i Search and pupulate listbox
so what I was looking for is to be able to filter it by Year and search it just by typing the whole numeric "2022" or numeric key "22"
or if its possible if i could only search between from "date" up to this "date"
Thanks in advance.
BTW: i am not an expert nor a coder, i only learn by reading, asking question and watching youtube guides
Note: "Year" is in separate column ("T")
the search box is working fine for other criteria, except for "Year" which is encoded only by Numeric (Ex. 2022)
for example if I filter it by "Year" then search for "2022"
It will not show any result even do there is a row/data that contains 2022 under Year column
but, if I edit the data and add a alphabet together with numeric ex "2022A"
and then filter it again by year and search. it will show the data .
for short it does not recognize numeric only in data unless it has an alphabet.
his is how i populate my combobox
Sub Refresh_DropDown_List()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("List")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data")
VBA Code:
Filter_by List'
With Me.cmb_Filter_By1
.Clear
.AddItem "ALL"
.AddItem "Year" '<--- i added this year according to column name in headers
.AddItem "Week"
.AddItem "Line"
.AddItem "Machine"
.AddItem "Description"
.AddItem "Possible Cause"
.AddItem "Corrective Action"
.AddItem "Action to be taken"
.AddItem "product"
.AddItem "Factor"
.AddItem "Status"
.AddItem "Incharge"
.AddItem "Note"
.Value = "ALL"
End With
End Subhis is how i Search and pupulate listbox
Code:
Sub Refresh_Listbox()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data_Display")
''''''''''' Copy Data ''''''''''
dsh.Cells.Clear
sh.AutoFilterMode = False
If Me.cmb_Filter_By2.Value <> "ALL" Then
sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_Filter_By2.Value, sh.Range("1:1"), 0), "*" & Me.txt_Search2.Value & "*"
End If
Dim lr As Long
On Error Resume Next
lr = Application.WorksheetFunction.CountA(dsh.Range("A:A"))
If lr = 1 Then lr = 2
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 25
.ColumnWidths = "35,33,30,100,70,44,60,120,120,120,120,70,100,50,70,70,70,70,200,50,100"
.TextAlign = fmTextAlignCenter
.RowSource = "Data_Display!A2:U" & lr
End With
End Subso what I was looking for is to be able to filter it by Year and search it just by typing the whole numeric "2022" or numeric key "22"
or if its possible if i could only search between from "date" up to this "date"
Thanks in advance.
BTW: i am not an expert nor a coder, i only learn by reading, asking question and watching youtube guides
