Listbox Filter Search

[Pacman52]

Hi all I'm just trying make a workbook more functional by hopefully adding a search textbox to the userform the contains the actual listbox. My knowledge of VBA is limited although I have a fairly good understanding of how to write code for basic stuff.

The overall purpose of the workbook is for my wife to enter expense and income information for her small business selling her products at festivals / events throughout the year.

Once populated the listbox will show all the events / festivals she has attended, it shows 10 Columns (out of 59 on the actual worksheet) with the key data event code, event name, location and start date listed in the first 4 columns with the other 6 columns containing general information.

What I'm hoping is achievable is to have a search box on the userform that can filter the listbox contents down to whatever criteria is entered in the search bar and once the relevant record is the only one visible in the listbox it can be double clicked to open the userform showing all the specific information for the record selected.

I have already (on another different workbook) created a search bar using a formula that filters the rows but the user can see the actual worksheets on this one, which is fine but with the workbook I'm asking about my wife will not be allowed to view any of the sheets.

I hope this makes sense what I'm after and logically I'm thinking it should be possible to do but I may be over complicating things so if anyone can suggest a more simplistic way of doing this I'm more than happy to try it.

Thanks for any responses Paul

 

[DanteAmor]

Here are a couple of examples to review.

Search by textbox and criteria combobox and show in listbox

Hello, good day everyone, I want to search based on the combobox as (search column) and textbox in sheet 1. How is this possible? ABCDEFGH1no.Nameidsexagedate of birthIQ TestMeaning of Name214Ani4935426663female2231/10/199998Ornament, Beautiful, Variant of Anne /...

www.mrexcel.com

 

[Pacman52]

Thanks for the reply sorry for not replying sooner I've been away. I will have a read through the thread link and let you know if its helpful.

Thanks again
Paul

 

[Pacman52]

Ok so I've read through that thread - could I just clarify what it does - the combo box contents show what the main criteria to search is based on the chosen listbox columns and then when entering something in the search box that only searches data that is selected in the combo box?

 

[DanteAmor]

Several pieces of information are missing from your OP:
- Sheet name, textbox name, listbox name
- Which 10 columns
- You have headers
- In which row the data begins
- In which column the data begins

I'm going to assume that you have header in row 1, that the data starts in cell A2, and that column A always has data.

The following code searches only for text (no numbers, no dates) in any cell from A2 to the last cell of the last row of the last column, if it finds a match then it adds the record to the listbox.

Try:

Option Explicit

Dim a As Variant

Private Sub TextBox1_Change()
  Dim txt As String
  Dim b As Variant
  Dim i As Long, j As Long, k As Long, m As Long
 
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  ListBox1.Clear
  
  k = k + 1
  For m = 1 To UBound(a, 2)
    b(1, m) = a(1, m)
  Next
  
  For i = 2 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      If TextBox1.Value = "" Then txt = a(i, j) Else txt = TextBox1.Value
      If LCase(a(i, j)) Like "*" & LCase(txt) & "*" Then
        k = k + 1
        For m = 1 To UBound(a, 2)
          b(k, m) = a(i, m)
        Next
        Exit For
      End If
    Next
  Next i
  If k > 0 Then ListBox1.List = b
End Sub

Private Sub userform_initialize()
  Dim sh As Worksheet
  Dim lr As Long, lc As Long
  
  Set sh = ThisWorkbook.Sheets("Sheet1")
  lr = sh.Range("A" & Rows.Count).End(3).Row
  lc = sh.Cells(1, Columns.Count).End(1).Column
  
  ListBox1.ColumnCount = lc
  a = sh.Range("A1", Cells(lr, lc)).Value
End Sub

Note: In the listbox you will see all the columns

 

[Pacman52]

thanks for the reply and the code a quick read through and your assumptions are nearly right. Whilst entering some test data I stumbled across a more pressing formatting problem (on another thread) so just trying to resolve this first as it effects the integrity of the data written to the sheet that in turn populates the listbox. If its ok with you can I come back to this thread once I've resolved the current problem.

Thanks Paul