PritishS
Board Regular
- Joined
- Dec 29, 2015
- Messages
- 119
- Office Version
- 2007
- Platform
- 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,
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
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 SubThis 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
. I made it working now. Additionally I have added one userform to show 'Record Searching...Please wait!!' which I load at the starting and unload at the ending using
. I did some modification to "search Col-A and Col-B both at a same time". I have used myField = 1 and myField = 2 as default.
