How to search data in listbox in vba

 

✅ 1. CheckBox1_Click Event

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
    CheckBox2.Value = False
    CheckBox3.Value = False
    TextBox1.Value = ""
    LoadListBox ""
End If
End Sub

What it does:

• When CheckBox1 is checked:

• It unchecks CheckBox2 and CheckBox3.

• Clears TextBox1 (removes any search text).

• Calls LoadListBox "" with an empty search term, which reloads data from Sheet1 (as set in LoadListBox).

• ✅ 2. CheckBox2_Click Event

  
  Private Sub CheckBox2_Click()
  If CheckBox2.Value = True Then
      CheckBox1.Value = False
      CheckBox3.Value = False
      TextBox1.Value = ""
      LoadListBox ""
  End If
  End Sub
  

  What it does:

  • When CheckBox2 is checked:

    • It unchecks CheckBox1 and CheckBox3.

    • Clears TextBox1.

    • Calls LoadListBox "" to reload data from Sheet2.

  ---

  ✅ 3. CheckBox3_Click Event

  
  Private Sub CheckBox3_Click()
  If CheckBox3.Value = True Then
      CheckBox1.Value = False
      CheckBox2.Value = False
      TextBox1.Value = ""
      LoadListBox ""
  End If
  End Sub
  

  What it does:

  • When CheckBox3 is checked:

  • Unchecks CheckBox1 and CheckBox2.

  • Clears TextBox1.

  • Loads data from Sheet3.

  • ✅ 4. TextBox1_Change Event

    
    Private Sub TextBox1_Change()
    LoadListBox TextBox1.Text
    End Sub
    

    What it does:

    • As the user types in the TextBox1, this event triggers and:

      • Passes the search text to the LoadListBox function to filter data in the list box.

    ---

    ✅ 5. UserForm_Initialize Event

    
    Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 10
    LoadListBox ""
    CheckBox3.Value = True
    End Sub
    

    What it does when the form loads:

    • Sets the ListBox1 to have 10 columns.

    • Loads the list box with no search filter (initial data from Sheet3).

    • Checks CheckBox3 by default.

    ---

    ✅ 6. LoadListBox(SearchTerm As String) Sub

    
    Private Sub LoadListBox(SearchTerm As String)
    

    Purpose:

    • Dynamically loads and filters data from the appropriate worksheet (Sheet1, Sheet2, or Sheet3) into ListBox1.

    Step-by-step breakdown:

    1. Determine the active sheet based on which checkbox is selected.

       
       If CheckBox1.Value = True Then
           Set ws = ThisWorkbook.Sheets("Sheet1")
       ElseIf CheckBox2.Value = True Then
           Set ws = ThisWorkbook.Sheets("Sheet2")
       ElseIf CheckBox3.Value = True Then
           Set ws = ThisWorkbook.Sheets("Sheet3")
       Else
           Exit Sub
       End If
       

    2. Find the last row with data in column A.

       
       lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
       

    3. Clear the ListBox to remove previous data.

       
       ListBox1.Clear
       

    4. Set column headers in the first row of ListBox1.

       
       .AddItem "Cust_Name"
       .List(0, 1) = "Email"
       ...
       

    5. Loop through rows (starting from row 2):

       • If SearchTerm is blank or matches any part of the customer name (column A), it:

         • Reads all 10 columns.

         • Adds the data to ListBox1.

       
       If SearchTerm = "" Then
           matchFound = True
       ElseIf InStr(1, LCase(ws.Cells(i, 1).Value), LCase(SearchTerm)) > 0 Then
           matchFound = True
       End If
       

    6. Know more about InStr click here

    7. Add matching data to ListBox1.

       
       ListBox1.AddItem rowData(1)
       For col = 2 To 10
           ListBox1.List(ListBox1.ListCount - 1, col - 1) = rowData(col)
       Next col
       

    ---

    ✅ Summary of Features:

  • A searchable list of customer data (from Sheet1/2/3) based on checkboxes.

  • A dynamic ListBox showing 10 columns with headers.

  • Search functionality via TextBox.

  • Mutually exclusive checkboxes to choose the source sheet.

  • Click here to download the practice file