Kapat

Filtering In Worksheet Using List Boxes

In this tutorial, we will filter data on the sheet with listbox and text boxes controls.
vba filter with listbox 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 : excel 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
excel populate listbox with unique sorted values

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 : excel vba autofilter based on textbox Sheets("Main").Range(Cells(1, 1), Cells(son, lst_column)).AutoFilter Field:=2, Criteria1:=TextBox1.Value & "*"

vba search in listbox 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 : excel search in listbox with textbox

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
excel search in listbox with textbox

excel sheet if exist 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 : excel vba sheet if exists

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 :
Private Sub CommandButton5_Click()
...
Call New_Sheet
...

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 :

excel vba filter with listbox

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.

📥 Download sample Excel workbook