Please advice how can I make Search with Criteria on Userform zie below macro
I appreciate beforehand your attention and time.
Sub SearchBox()
Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant
Set sht = ActiveSheet
On Error Resume Next
sht.ShowAllData
On Error GoTo 0
Set DataRange = sht.Range("A4:E160") 'Cell Range
mySearch = sht.Shapes("Search").TextFrame.Characters.Text 'Control Form
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If
For Each myButton In sht.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If
Next myButton
myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
DataRange.AutoFilter _
Field:=myField, _
Criteria1:=SearchString, _
Operator:=xlAnd
sht.Shapes("Search").TextFrame.Characters.Text = "" 'Control Form
End Sub
Bookmarks