How to Create a filtering search box with more than 1 key word

PritishS

Board Regular
Joined
Dec 29, 2015
Messages
119
Office Version
  1. 2007
Platform
  1. Windows
Dear Sir/Madam,
Good Day!

My requirement is to make a filtering search box in my whroksheet so user can type desired word and it should appear with filtered row only.

I googled it and found a really helpful post from Mr. Chris Newman. This great code fulfilled my requirement 90%.
Here is the Link for anyone looking for something like this-
http://www.thespreadsheetguru.com/blog/2014/11/3/filtering-search-box

I modified this code to match my excel sheet. Here is the code. All credit goes to Mr. Newman,

Code:
Sub SearchBox()'PURPOSE: Filter Data on User-Determined Column & Text
'SOURCE: www.TheSpreadsheetGuru.com


Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant
   
'Load Sheet into A Variable
  Set sht = ActiveSheet


'Unfilter Data (if necessary)
  On Error Resume Next
    sht.ShowAllData
  On Error GoTo 0
   
'Filtered Data Range (include column heading cells)
  Set DataRange = sht.Range("A3:D400000") 'Cell Range
  'Set DataRange = sht.ListObjects("Table1").Range 'Table


'Retrieve User's Search Input
  'mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
  mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
  'mySearch = sht.Range("A1").Value 'Cell Input


'Loop Through Option Buttons
  For Each myButton In ActiveSheet.OptionButtons
      If myButton.Value = 1 Then
        ButtonName = myButton.Text
        Exit For
      End If
  Next myButton
  
'Determine Filter Field
  On Error GoTo HeadingNotFound
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
  On Error GoTo 0
  
'Filter Data
  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:="*" & mySearch & "*", _
    Operator:=xlAnd
  
'Clear Search Field
  'sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
  sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
  'sht.Range("A1").Value = "" 'Cell Input


Exit Sub


'ERROR HANDLERS
HeadingNotFound:
  MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
    vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
    
End Sub

This search criteria searches adjacent keywords of a defined column name. Ex:- In col-A there is sentence-'I have a Pen'. I can write in search box-'I','have','a pen','have a,'I have'. But If I enter 'I Pen' or 'have Pen', it's returning no result. Can anyone please guide me any modification which can be done in this code to match my requirement? Hoping for the best. Have a Nice Day!!
Regards,
PritishS
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
This uses the AdvancedFilter function instead of Autofilter to match all search words in the search criteria. It builds a temporary search criteria table in cells AA1:AZ2 that is used by the AdvancedFilter function.

Caveat: If you search for "I", it matches the letter "I" and not the word "I". So for each word in the search criteria, it could have a partial word match in the sentences of each cell.

Code:
[color=darkblue]Sub[/color] SearchBox()    [color=green]'PURPOSE: Filter Data on User-Determined Column & Text[/color]
[color=green]'SOURCE: www.TheSpreadsheetGuru.com[/color]
    
    [color=darkblue]Dim[/color] myButton  [color=darkblue]As[/color] OptionButton
    [color=darkblue]Dim[/color] MyVal     [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] ButtonName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] sht       [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] myField   [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] DataRange [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] mySearch  [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] vMySearch [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    
    [color=green]'Load Sheet into A Variable[/color]
    [color=darkblue]Set[/color] sht = ActiveSheet
    
    [color=green]'Unfilter Data (if necessary)[/color]
    [color=darkblue]If[/color] sht.FilterMode [color=darkblue]Then[/color] sht.ShowAllData
    
    [color=green]'Filtered Data Range (include column heading cells)[/color]
    [color=darkblue]Set[/color] DataRange = sht.Range("A3:D40000")    [color=green]'Cell Range[/color]
    [color=green]'Set DataRange = sht.ListObjects("Table1").Range 'Table[/color]
    
    [color=green]'Retrieve User's Search Input[/color]
    [color=green]'mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form[/color]
    mySearch = sht.OLEObjects("UserSearch").Object.Text    [color=green]'ActiveX Control[/color]
    [color=darkblue]If[/color] mySearch = "" [color=darkblue]Then[/color] MsgBox "Search box is empty", vbExclamation, "Invalid Search Criteria": [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=green]'mySearch = sht.Range("A1").Value 'Cell Input[/color]
    
    [color=green]'Loop Through Option Buttons[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] myButton [color=darkblue]In[/color] ActiveSheet.OptionButtons
        [color=darkblue]If[/color] myButton.Value = 1 [color=darkblue]Then[/color]
            ButtonName = myButton.Text
            [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] myButton
    
    [color=green]'Determine Filter Field[/color]
    myField = Application.Match(ButtonName, DataRange.Rows(1), 0)
    [color=darkblue]If[/color] IsError(myField) [color=darkblue]Then[/color] [color=darkblue]GoTo[/color] HeadingNotFound
    [color=green]'myField = 1 'For testing[/color]
    
    [color=green]'Create temp filter criteria table[/color]
    Range("AA1:AZ2").ClearContents
    vMySearch = Split(Replace("*" & Application.Trim(mySearch) & "*", " ", "* *"), " ")
    Range("AA1").Resize(, [color=darkblue]UBound[/color](vMySearch) + 1).Value = DataRange(1, myField)
    Range("AA2").Resize(, [color=darkblue]UBound[/color](vMySearch) + 1).Value = vMySearch
    
    [color=green]'Filter Data[/color]
    DataRange.AdvancedFilter xlFilterInPlace, Range("AA1:AZ2")
    
    [color=green]'clear temp filter criteria table[/color]
    Range("AA1:AZ2").ClearContents
    
    [color=green]'    DataRange.AutoFilter _
    '            Field:=myField, _
    '            Criteria1:="*" & mySearch & "*", _
    '            Operator:=xlAnd[/color]
    
    [color=green]'Clear Search Field[/color]
    [color=green]'sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form[/color]
    sht.OLEObjects("UserSearch").Object.Text = ""    [color=green]'ActiveX Control[/color]
    [color=green]'sht.Range("A1").Value = "" 'Cell Input[/color]
    
    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=green]'ERROR HANDLERS[/color]
HeadingNotFound:
    MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
           vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Dear Sir,

Thank you very much for your Valuable time and response. I'll check it now and get back to you asap.

Thanks & Regards,
PritishS
 
Upvote 0
Here's another method that will only match whole words or phrases. So "I" will only match the word "I" and not the letter "I". It uses column AA to set a True\False value if it matched the criteria and then Autofilters the column for True.

To search for separate words or phrases, use a comma in the search box to separate them e.g.; I, pen
Or you could search for say; I have, pen and it would match I have a red pen

Code:
[color=darkblue]Sub[/color] SearchBox()    [color=green]'PURPOSE: Filter Data on User-Determined Column & Text[/color]
[color=green]'SOURCE: www.TheSpreadsheetGuru.com[/color]
    
    [color=darkblue]Dim[/color] myButton  [color=darkblue]As[/color] OptionButton
    [color=darkblue]Dim[/color] MyVal     [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] ButtonName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] sht       [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] myField   [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] DataRange [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] mySearch  [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] vMySearch [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] vData [color=darkblue]As[/color] [color=darkblue]Variant[/color], Crit [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=green]'Load Sheet into A Variable[/color]
    [color=darkblue]Set[/color] sht = ActiveSheet
    
    [color=green]'Filtered Data Range (include column heading cells)[/color]
    [color=darkblue]Set[/color] DataRange = sht.Range("A3:D40000")    [color=green]'Cell Range[/color]
    [color=green]'Set DataRange = sht.ListObjects("Table1").Range 'Table[/color]
    
    [color=green]'Retrieve User's Search Input[/color]
    [color=green]'mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form[/color]
    mySearch = sht.OLEObjects("UserSearch").Object.Text    [color=green]'ActiveX Control[/color]
    [color=darkblue]If[/color] mySearch = "" [color=darkblue]Then[/color] MsgBox "Search box is empty", vbExclamation, "Invalid Search Criteria": [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    [color=green]'mySearch = sht.Range("A1").Value 'Cell Input[/color]
    
    [color=green]'Loop Through Option Buttons[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] myButton [color=darkblue]In[/color] ActiveSheet.OptionButtons
        [color=darkblue]If[/color] myButton.Value = 1 [color=darkblue]Then[/color]
            ButtonName = myButton.Text
            [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] myButton
    
    [color=green]'Determine Filter Field[/color]
    myField = Application.Match(ButtonName, DataRange.Rows(1), 0)
    [color=darkblue]If[/color] IsError(myField) [color=darkblue]Then[/color] [color=darkblue]GoTo[/color] HeadingNotFound
    [color=green]'myField = 1 'For testing[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=green]'Unfilter Data (if necessary)[/color]
    [color=darkblue]If[/color] sht.FilterMode [color=darkblue]Then[/color] sht.ShowAllData
    
    [color=green]'Split search tems by comma[/color]
    vMySearch = Split(Application.Trim(mySearch), ",")
    vData = DataRange.Columns(myField).Value
    [color=green]'Loop through array and Test for all search terms[/color]
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vData, 1)
        [color=darkblue]For[/color] [color=darkblue]Each[/color] Crit [color=darkblue]In[/color] vMySearch
            [color=darkblue]If[/color] [color=darkblue]Not[/color] (" " & LCase(vData(i, 1)) & " ") [color=darkblue]Like[/color] "* " & LCase(Application.Trim(Crit)) & " *" [color=darkblue]Then[/color] vData(i, 1) = False: [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=darkblue]Next[/color] Crit
        [color=darkblue]If[/color] vData(i, 1) <> [color=darkblue]False[/color] [color=darkblue]Then[/color] vData(i, 1) = [color=darkblue]True[/color]
    [color=darkblue]Next[/color] i
    [color=green]'Write true\false results to column AA and filter for true[/color]
    [color=darkblue]With[/color] Range("AA3").Resize(UBound(vData))
        .Value = vData
        .AutoFilter Field:=1, Criteria1:="True"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=green]'Clear Search Field[/color]
    [color=green]'sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form[/color]
    sht.OLEObjects("UserSearch").Object.Text = ""    [color=green]'ActiveX Control[/color]
    [color=green]'sht.Range("A1").Value = "" 'Cell Input[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=green]'ERROR HANDLERS[/color]
HeadingNotFound:
    MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
           vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Dear Sir,

Thank you very much!! I have tested your first code and got the result as expected. My table heading starts from Row 3 so I modified this as per my requirement. So I changed my filter range from AA1:AZ2 to AA3:AZ4. But here some some points I'd like to bring your kind notice.

1. My sheet is protected with password. (I managed it unlocking and locking with my password at the beginning and ending of this code).
2. My table already had a filter applied (for searching data using filter) on heading 'A3 to F/G/H3...etc.(no. of columns varies). I found after click on search button, its giving my result as typed but FILTER is getting removed. As this sheet is protected, users are not able to apply filter again(I have given a button to reset filter for users). Is there any way that those filter will be remain as it is? More clearly pre-defined heading filters should not be removed while clicking on 'Search' button.

3. This is a follow up question: Is there any way to re-search data from filtered data only. For example- first I will enter 'Pen' in searchbox and click on 'Search' button. Now all result with 'Pen' will be shown. Now if I enter 'Red' in search box and click enter then it should show all records with 'Pen Red'. But now it is resetting the filtered result and only showing results which have 'Red'. I tried using, but no luck

Code:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#303336]Set DataRange = sht[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]"A1:A100"[/COLOR][COLOR=#303336]).[/COLOR][COLOR=#303336]Rows[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]SpecialCells[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]xlCellTypeVisible[/COLOR][COLOR=#303336])
[/COLOR]</code>

Please guide me how to achieve those. Thanks in advance!! Have a good Day!

Thanks & Regards,

PritishS
 
Upvote 0
Dear Sir,

I have tried adding this code to apply filter on row3 at the end of the code. It is applying filter but it removes filtering.

Code:
'Select Row3 and Auto filter    Rows("3:3").Select
    Selection.AutoFilter
    Range("A3").Select

With my beginner knowledge I expected to get best result but no luck. Hope there will be another way to do that.


Thanks & Regards,

PritishS
 
Upvote 0
The code below uses an empty helper column 5 (column E). Change the column number to suit. You must include this helper column in your Autofilter range.

Change the sheet password to suit.

Code:
[COLOR=darkblue]Sub[/COLOR] SearchBox()    [COLOR=green]'PURPOSE: Filter Data on User-Determined Column & Text[/COLOR]
[COLOR=green]'SOURCE: www.TheSpreadsheetGuru.com[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] myButton  [COLOR=darkblue]As[/COLOR] OptionButton
    [COLOR=darkblue]Dim[/COLOR] ButtonName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sht       [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] myField   [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] mySearch  [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vMySearch [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] DataRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Crit [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=green]'Helper column used to filter Search terms[/COLOR]
    [COLOR=green]'Empty coluumn included in the Autofilter Range[/COLOR]
    [COLOR=green]'Optional: column can be hidden[/COLOR]
    [COLOR=darkblue]Const[/COLOR] FilterColumn [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] = [COLOR=#ff0000]5[/COLOR]
    
    [COLOR=green]'Load Sheet into A Variable[/COLOR]
    [COLOR=darkblue]Set[/COLOR] sht = ActiveSheet
    
    [COLOR=green]'Filtered Data Range (include column heading cells)[/COLOR]
    [COLOR=darkblue]Set[/COLOR] DataRange = sht.AutoFilter.Range    [COLOR=green]'Cell Range[/COLOR]
    [COLOR=green]'Set DataRange = sht.ListObjects("Table1").Range 'Table[/COLOR]
    
    [COLOR=green]'Retrieve User's Search Input[/COLOR]
    [COLOR=green]'mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form[/COLOR]
    mySearch = sht.OLEObjects("UserSearch").Object.Text    [COLOR=green]'ActiveX Control[/COLOR]
    [COLOR=darkblue]If[/COLOR] mySearch = "" [COLOR=darkblue]Then[/COLOR] MsgBox "Search box is empty", vbExclamation, "Invalid Search Criteria": [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=green]'mySearch = sht.Range("A1").Value 'Cell Input[/COLOR]
    
    [COLOR=green]'Loop Through Option Buttons[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] myButton [COLOR=darkblue]In[/COLOR] ActiveSheet.OptionButtons
        [COLOR=darkblue]If[/COLOR] myButton.Value = 1 [COLOR=darkblue]Then[/COLOR]
            ButtonName = myButton.Text
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] myButton
    [COLOR=green]'Determine Filter Field[/COLOR]
    myField = Application.Match(ButtonName, sht.AutoFilter.Range.Rows(1), 0)
     'myField = 1    [COLOR=green]'For testing[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsError(myField) [COLOR=darkblue]Then[/COLOR]
    
        Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
        ActiveSheet.Unprotect Password:="Secret"
    
        [COLOR=green]'Split search tems by comma[/COLOR]
        vMySearch = Split(Replace("*" & Application.Trim(mySearch) & "*", " ", "* *"), " ")
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] DataRange.Columns(myField).SpecialCells(xlCellTypeVisible)
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] Crit [COLOR=darkblue]In[/COLOR] vMySearch
                [COLOR=darkblue]If[/COLOR] " " & LCase(cell.Value) & " " [COLOR=darkblue]Like[/COLOR] "* " & LCase(Application.Trim(Crit)) & " *" [COLOR=darkblue]Then[/COLOR]
                    Cells(cell.Row, FilterColumn).Value = [COLOR=darkblue]True[/COLOR]
                    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    Cells(cell.Row, FilterColumn) = ""
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] Crit
        [COLOR=darkblue]Next[/COLOR] cell
        DataRange.AutoFilter Field:=FilterColumn, Criteria1:="True"
    
        [COLOR=green]'Clear Search Field[/COLOR]
        [COLOR=green]'sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form[/COLOR]
        sht.OLEObjects("UserSearch").Object.Text = ""    [COLOR=green]'ActiveX Control[/COLOR]
        [COLOR=green]'sht.Range("A1").Value = "" 'Cell Input[/COLOR]
        
        Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
        ActiveSheet.Protect Password:="Secret"
    
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
               vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Resets to original autofilter critera

Code:
[COLOR=darkblue]Sub[/COLOR] SearchReset()
    [COLOR=darkblue]Const[/COLOR] FilterColumn [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR] = [COLOR=#ff0000]5[/COLOR]
    
    ActiveSheet.Unprotect Password:="Secret"
    
    ActiveSheet.Columns(FilterColumn).Replace What:="TRUE", _
                                              Replacement:="", _
                                              SearchOrder:=xlByRows, _
                                              MatchCase:=[COLOR=darkblue]False[/COLOR]
    
    ActiveSheet.AutoFilter.Range.AutoFilter Field:=FilterColumn, Criteria1:=""
    
    ActiveSheet.Protect Password:="Secret"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
Dear Sir,

Thanks for your kind help!! Let me test this code and I'll get back to you asap.

Thanks & Regards,

PritishS
 
Upvote 0
Hello Sir!!

Hope you are doing well!
Your last suggested code is working great and solved my question no 2 and 3. Thank You very much!!
I have a little doubt.

1st Code:

In this code, searching was like google search. I can write words in any sequence, Upper/Lower case or any combinations, it was filtering the exact row as I typed. But problem was 1. It was removing my pre-set filter on 3rd row and 2. It was not able to search within filtered range. i.e. for new search string it was unfiltered the table and then searched for new string.

Where in Latest Code.

This 2 issues has been solved. But I found that I can not write few words at a time as I was able to do that in 1st Code. However I'm able to to enter few words separated by coma(,) sign, but it is not filtering the exact data like 1st code.

My question:

Can I somehow merge 1st code and Latest code so that my below mentioned requirements gets fulfilled-

1. Search entering any word in any sequence at a time to filter. Ex- 'I Pen have', 'I a have', 'I red have'..etc. will give me the result 'I Have a Red Pen'. (This is possible in 1st Code).

2. Searching row based on entering one word at a time and click on search and then if I enter the second word, it should search from filtered data range. (This is possible in latest code).
Ex- if I enter 'Have' and search all row having 'Have' will filter. then if I enter 'Pen', all result with 'Have Pen' will be filtered.
This method is Okay, only user have to enter 1 word at a time and every time he/she has to click on 'Search' button. Sometime user may feel annoyed for clicking search button after each word.

3. Pre-Set filter should not be removed as this sheet is password protected.

Can you please guide me how to have all three criteria in a single code? Thanks in advance. I'm really happy to learn new thing in excel vba everyday

One more thing for others who are following this discussion, for the latest code i need to first unprotect the sheet with ActiveSheet.Unprotect Password:="Password" and protect the sheet after operation with ActiveSheet.Protect Password:="Password".
Once I locked this sheet I found I was unable to click on filter on columns. With locking the worksheet protected the filter option too.

So I just modified
Code:
ActiveSheet.Protect Password:="Password"
to
Code:
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, UserInterfaceOnly:=True
in this code.

Thanks & Regards,

PritishS
 
Upvote 0
In the latest code, change this...
Code:
        [COLOR=green]'Split search tems by comma[/COLOR]
        vMySearch = Split(Replace("*" & Application.Trim(mySearch) & "*", " ", "* *"), " ")

To this...
Code:
        [COLOR=green]'Split search terms by space[/COLOR]
        vMySearch = Split(Application.Trim(mySearch), " ")
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top