jddurocher
New Member
- Joined
- Jun 21, 2015
- Messages
- 1
Never posted here before, so hoping for a good first experience.
With some help I've put together some coding to create a search field (like on google) to go through a spreadsheet containing customer feedback. i can key in a specific word and if the word appears within the range, then the spreadsheet acts like a data filter and hides other rows to show only the rows with my key word. if there's anyone out there that's Coding Savvy, have a look at my script and if you could provide some insight, it would be greatly appreciated!!!!!!
here is a sample of the 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("I5:X52") '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
myField = WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 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
End Sub
With some help I've put together some coding to create a search field (like on google) to go through a spreadsheet containing customer feedback. i can key in a specific word and if the word appears within the range, then the spreadsheet acts like a data filter and hides other rows to show only the rows with my key word. if there's anyone out there that's Coding Savvy, have a look at my script and if you could provide some insight, it would be greatly appreciated!!!!!!
here is a sample of the 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("I5:X52") '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
myField = WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 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
End Sub
Last edited:
