Adding a search box to an Excel sheet using VBA (very novice)

[lkeatley]

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.

Capture.jpg

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!

[ValerieT]

Try that

Dim MySearch As String
MySearch = "=*" & Cells(1, 4) & "*"
ActiveSheet.Range("$A$1:$A$10500").AutoFilter Field:=1, Criteria1:=MySearch, Operator:=xlAnd

[lkeatley]

Thanks Valerie, this still isn't working..

Says

Compile Error:

Invalid outside procedure

Can you please tell me what I am doing wrong??