Search by simply typing the first letter

[Afarag]

Dears,

Please i have a column contain a lot of name, i want to create text box, which i will write the name that i want to find, that i want a VBA can make this filter that Search by typing the first letter and another VBA can remove this filter after that

Gratefully,

 

[Luke M]

I don't completely understand your request, but if you create an ActiveX combobox, you can set your list as the data source, and set the "AutoComplete" property to True. Is this what you want?

 

[Afarag]

Regardless of ActiveX combobox, you have to create the best but what i want is when i start typing the name a filter will be applied to give my required name

as like this macro

 

[NARAYANK991]

Hi ,

Check this file.

Narayan

 

[Afarag]

@NARAYANK991

Kindly that's exactly what i look for.

Thank you so much.

 

[bharath]

it is too good to finding names

 

[Afarag]

@NARAYANK991

could you provide me how can i insert the text box

 

[NARAYANK991]

Hi ,

Can you please explain what the problem is ? Do you want to know how to insert the text box ? If so , click on the Developer tab , click on Insert , hover over each control in the section labelled ActiveX controls , and click on the one labelled Text Box (ActiveX Control).

Narayan

 

[Afarag]

i was want the method of creating this auto-filter

thanks

 

[NARAYANK991]

Hi ,

The code itself is straightforward ; there are two modules :

Private Sub TextBox1_Change()
            Range("A2").AutoFilter 1, "*" & [A1] & "*"
End Sub

This is an event procedure , which is invoked whenever any change is made in the text box ; whenever you add / change / delete characters in the text box , this code runs the in-built AutoFilter , with whatever characters are available in the text box ; the linked cell of the text box has been put to cell A1 , whose cell format has been put to a white font so that the characters are not visible. Any text that is entered in the text box is immediately available in cell A1 ; the criterion for the AutoFilter is :

"*" & [A1] & "*"

which means the text string entered in the text box can be embedded anywhere in the database cells. Thus a text string of amdy will match the following entries :

Fatmaalzahraa Hamdy Fathy Ahmed
Haytham Hamdy Gamal

The other module is :

Sub Button4_Click()
    Sheet1.AutoFilterMode = False
    Sheet1.TextBox1.Text = ""
End Sub

This resets the AutoFilter so that all entries are displayed once more ; also the text which had been entered in the text box is cleared so that fresh data can be entered.

Narayan

 

[inddon]

Hi ,

The code itself is straightforward ; there are two modules :

Private Sub TextBox1_Change()
            Range("A2").AutoFilter 1, "*" & [A1] & "*"
End Sub

This is an event procedure , which is invoked whenever any change is made in the text box ; whenever you add / change / delete characters in the text box , this code runs the in-built AutoFilter , with whatever characters are available in the text box ; the linked cell of the text box has been put to cell A1 , whose cell format has been put to a white font so that the characters are not visible. Any text that is entered in the text box is immediately available in cell A1 ; the criterion for the AutoFilter is :

"*" & [A1] & "*"

which means the text string entered in the text box can be embedded anywhere in the database cells. Thus a text string of amdy will match the following entries :

Fatmaalzahraa Hamdy Fathy Ahmed
Haytham Hamdy Gamal

The other module is :

Sub Button4_Click()
    Sheet1.AutoFilterMode = False
    Sheet1.TextBox1.Text = ""
End Sub

This resets the AutoFilter so that all entries are displayed once more ; also the text which had been entered in the text box is cleared so that fresh data can be entered.

Narayan

Hello Narayan,

This is good solution, specially for non-excel users.

Could you please let me know how this textbox find functionality, can be implemented when you have a table? (eg. Table Name:Table1, Table Column Name: Invoice, Data row starts from row: 6)


Looking forward to hearing from you.

Thanks & regards
Don

 

[Afarag]

Hi inddon

there isn't any problem to use @NARAYANK991 code to auto filter a basic table, but if you mean to use this feature with a pivot table, you have to use the below code

Private Sub TextBox1_Change()
    Dim pi As PivotItem, pf As PivotField
    Set pf = ThisWorkbook.Worksheets("Sheet1").PivotTables("Table1").PivotFields("Invoice")
    For Each pi In pf.PivotItems
        If InStr(1, pi.Value, TextBox1.Value, vbTextCompare) > 0 Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next
End Sub

make sure the worksheet name then don't forget to set the linked cell to "from textbox properties" to be "A5" if your data are filled in column "A"


@NARAYANK991 need your support if is there misunderstanding in this issue