lkeatley
04-01-2015, 06:57 PM
Hi there,
I am trying to set up a search box in Excel using VBA code
What I want it to do is to search within column A (named range: Name, goes from A2:A10500) and return a result based on the search text... ideally it would be good to show results that it "could" be.. e.g instead of writing "Aaron Smith" someone could write "Aaron" which would show the Aarons within the database.
I have created a search box and a search button which the VBC code will be assigned to.
13108
Can someone please point me towards a solution for a VBA code
Currently I have:
Sub SearchBox()
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
'Unfilter Data (if necessary)
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
'Filtered Data Range
Set DataRange = Range("A4:A10500")
'Filter Data
DataRange.AutoFilter _
Field:="Name", _
Criteria1:="=*" & ActiveSheet.Shapes("UserSearch").TextFrame.Characters.Text & "*", _
Operator:=xlAnd
'Clear Search Field
ActiveSheet.Shapes("UserSearch").TextFrame.Characters.Text = ""
End Sub
Which comes up with a 400 error.
Please please help! :banghead:
I am trying to set up a search box in Excel using VBA code
What I want it to do is to search within column A (named range: Name, goes from A2:A10500) and return a result based on the search text... ideally it would be good to show results that it "could" be.. e.g instead of writing "Aaron Smith" someone could write "Aaron" which would show the Aarons within the database.
I have created a search box and a search button which the VBC code will be assigned to.
13108
Can someone please point me towards a solution for a VBA code
Currently I have:
Sub SearchBox()
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
'Unfilter Data (if necessary)
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
'Filtered Data Range
Set DataRange = Range("A4:A10500")
'Filter Data
DataRange.AutoFilter _
Field:="Name", _
Criteria1:="=*" & ActiveSheet.Shapes("UserSearch").TextFrame.Characters.Text & "*", _
Operator:=xlAnd
'Clear Search Field
ActiveSheet.Shapes("UserSearch").TextFrame.Characters.Text = ""
End Sub
Which comes up with a 400 error.
Please please help! :banghead: