Search by textbox and criteria combobox and show in listbox

[mredvard]

Hello, good day everyone, I want to search based on the combobox as (search column) and textbox in sheet 1.
How is this possible?

search by combo box.xlsm
	A	B	C	D	E	F	G	H
1	no.	Name	id	sex	age	date of birth	IQ Test	Meaning of Name
2	14	Ani	4935426663	female	22	31/10/1999	98	Ornament, Beautiful, Variant of Anne / Ane
3	15	Anna	4037366181	female	20	26/10/2001	126	‘Grace’. Also used as a nickname for Annabelle
4	16	Anya	9330871997	female	25	12/04/1997	109	Inexhaustible, Gracious, Graceful
5	17	Ariel	3660975387	female	25	27/01/1997	127	‘Lion of God’. It is also spelt as Arielle.
6	18	Arya	9935838857	female	23	17/07/1999	108	‘Lioness, Noble’. It is frequently spelt as Aria.
7	19	Ash	3882405562	female	25	14/12/1996	96	Form of Ashley, Ash Tree Meadow
8	20	Ashley	7564224317	female	26	03/03/1996	99	‘One who lives in the ash tree grove’. It is one of the modern English baby girl names.
9	21	Audrey	4121552927	female	18	04/11/2003	111	Strength
10	22	Ava	6560141181	female	19	29/10/2002	124	‘Bird’ or ‘the living one’ or ‘life’. It may also be spelt as Eva.
11	23	Badra	8004003618	female	27	10/05/1995	114	Full Moon, Pretty, Goddess Durga
12	24	Beatrice	6870820925	female	20	11/02/2002	91	‘She who makes you happy’. It is also spelt Beatrix.
13	25	Belinda	3658130608	female	24	09/06/1998	119	‘Beautiful’. One of its other variants is ‘Bella’. ‘Bright linden tree’ or ‘bright serpent’.
14	26	Bella	2725479507	female	18	20/09/2003	122	‘Beautiful’. It is often used as a nickname for other names ending in –bella, like Annabella, Arabella, etc.
15	27	Bency	5191837585	female	26	06/04/1996	111	Right Hand of Jesus Christ, A Talkative Girl who is Very Scared
16	28	Berey	4061161891	female	20	12/05/2002	107	Exalted One, Bearer of Victory, True Image, To Help, Berry
17	29	Bery	1556003492	female	22	14/12/1999	129	True Image, Bearer of Victory, Berry, To Help, Exalted One
18	30	Bety	2871809184	female	20	01/09/2002	119	God is My Oath, God is Perfection
19	31	Bitsy	2218154671	female	22	17/11/1999	109	God is Perfection, God is My Oath
20	32	Brianna	5026781508	female	21	20/08/2001	123	‘Strong’
sheet1

 

[DanteAmor]

Hi and welcome to MrExcel

Check this:

Userform listbox search

So I have a user form that has a combo box that filters the data with the codes Private Sub Combobox1_change() Me.ListBox1.Clear For i = 2 To Application.WorksheetFunction.CountA(Sheet3.Range("D:D")) If Sheet3.Cells(i, "D").Value = Me.ComboBox1.Value Then Me.ListBox1.AddItem Sheet3.Cells(i...

www.mrexcel.com

 

[mredvard]

hi bro tank u for answer
I made this based on what you said, the form runs without errors
But it does not search for anything
When something is selected from the list box or when something is written in the text box, nothing happens in the list box

what is the reason?

i

Option Explicit

Dim a As Variant

Private Sub Combobox1_change()
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  Call FilterData
End Sub

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

Private Sub userform_initialize()
  Dim ws As Worksheet
    Dim comboValues As Variant
    Set ws = ThisWorkbook.Sheets("sheet1")
    Dim lastRow As Long
    Dim dataRange As Range

     ComboBox1.List = Array(ws.Cells(1, 1).Value, ws.Cells(1, 2).Value, ws.Cells(1, 3).Value, ws.Cells(1, 4).Value, ws.Cells(1, 5).Value, ws.Cells(1, 6).Value, ws.Cells(1, 7).Value, ws.Cells(1, 8).Value)
ListBox1.ColumnCount = 4

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

Set dataRange = ws.Range("A2:H" & lastRow)
a = dataRange.Value
End Sub

 

[mredvard]

@DanteAmor​

I want to select the search condition based on the list box and then enter the value in the text box and after search result show in listbox

 

[DanteAmor]

You need to adapt the code to the columns where you are going to perform the search.

In which column do you want to search for what you select in the combobox?

In which column do you want to search for what you select in the Textbox?

 

[DanteAmor]

I want to select the search condition based on the list box and then enter the value in the text box and after search result show in listbox

Replace all your code for this:

Option Explicit

Dim a As Variant

Private Sub Combobox1_change()
  Call FilterData
End Sub

Private Sub TextBox1_Change()
  Call FilterData
End Sub

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

Private Sub userform_initialize()
  Dim ws As Worksheet
  
  Set ws = ThisWorkbook.Sheets("sheet1")
  ComboBox1.List = Application.Transpose(ws.Range("A1:H1").Value)
  ListBox1.ColumnCount = 8
  a = ws.Range("A2:H" & ws.Range("A" & Rows.Count).End(xlUp).Row).Value
End Sub

 

[mredvard]

@DanteAmor​

muchas gracias
la tarjeta estuvo genial

 

[mredvard]

Hello، my good friend.
If I don't want to show anything in the listbox ,until I select the Combobox and enter the word in text box, where should I change the code?
when i select one item in combobox the list box is populate
i used this but its not work

 If k > 0 And textbox1.value> 1 Then ListBox1.List = b

Is it possible to do something that if something is inserted in the textbox، it will be in the whole column، and if the combo box is selected، it will search only in that column.

 

[DanteAmor]

If I don't want to show anything in the listbox ,until I select the Combobox and enter the word in text box

For that.
After this line:
If ComboBox1.ListIndex = -1 Then Exit Sub

Add this line:
If TextBox1.Value = "" Then Exit Sub

Is it possible to do something that if something is inserted in the textbox، it will be in the whole column، and if the combo box is selected، it will search only in that column.

The search already does that, it only searches the column selected in the combo.

 

[mredvard]

The search already does that, it only searches the column selected in the combo.

inserted in the textbox، it will be in the whole columns
tank u man for your answer