In this tutorial, we will filter data on the sheet with listbox and text boxes controls.
We have a spreadsheet ; it’s column headeds Code, Machine, Product Brand, Features, Price.
We have added for machine, product brand, product properties columns 3 listbox on the userform.We filled the values in machine column into 1.listbox, product brand column’s data into 2nd listbox, and properties column’s data into 3rd listbox as unique and order.
Our VBA codes for to populate listbox unique sorted values :
With CreateObject("Scripting.Dictionary") For Each hcr In Sheets("Main").Range("B3:B" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeVisible) If Not .exists(hcr.Value) Then .Add hcr.Value, Nothing End If Next hcr a = .keys End With For i = LBound(a) To UBound(a) - 1 For j = i + 1 To UBound(a) If StrComp(a(i), a(j)) = 1 Then x = a(j) a(j) = a(i) a(i) = x End If Next j Next i On Error Resume Next ListBox1.List = a
There is a textbox control for each listbox. The selected item from the listbox is displayed on the textbox.AutoFilter method is triggered according to the value in the textbox .To filter in column B based on TextBox1’s value :
Sheets("Main").Range(Cells(1, 1), Cells(son, lst_column)).AutoFilter Field:=2, Criteria1:=TextBox1.Value & "*"
With text boxes, the first character of the cell or in whole of cell ,value can be searched in the listbox based on the selection of option buttons. VBA codes to search with textbox in listbox :
If OptionButton1.Value = True Then arama = LCase(TextBox1.Text & "*") If OptionButton2.Value = True Then arama = LCase("*" & TextBox1.Text & "*") If TextBox1 = Empty Then Call lsbox1 End If With ListBox1 For i = .ListCount - 1 To 0 Step -1 If Not (LCase(.List(i, 0)) Like arama) Then .RemoveItem i End If Next i End With
We added a “Transfer Filtered Data” button to the userform to copy the data filtered on the sheet to other sheet (The_Filtered_Data sheet). Before copying the filtered data, it is checked whether The_Filtered_Data sheet is available with the help of a function.
Test or check if sheet exists in Excel :
Sub New_Sheet() If Not Sheet_Exists_Contrl("The_Filtered_Data") Then ThisWorkbook.Sheets.Add( _ After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = "The_Filtered_Data" End If End Sub Function Sheet_Exists_Contrl(SheetName As String) As Boolean Dim sht As Excel.Worksheet On Error GoTo eHandle Set sht = ThisWorkbook.Worksheets(SheetName) Sheet_Exists_Contrl = True Exit Function eHandle: Sheet_Exists_Contrl = False End Function
For calling the function in the codes of Transfer Filtered Data button :
If there is no “The_Filtered_Data” sheet, the worksheet with this name is created by “New_Sheet” procedure, if there is “The_Filtered_Data” sheet in the workbook, previously copied data is deleted and the new data is copied into this sheet.
Let’s try the filtering process on the worksheet :
User can use the userform in its own worksheets. For this, user need to add the userform (dependt_listbx_Form) to its workbook. Also, in the codes in the VBA Window, the worksheet name (sheets (“Main”), the column names (B, C, D columns) must be changed to fill the listboxes.