Response to txtbox content if not found in the worksheets: Excel V

[Msgbox Data not found]

Hi,
Everyone,

I've a worksheet in a workbook that has a Search text Box created from the
control box.
I'd like to know the code that generates Msgbox "Data not found!!", when the
search button is clicked.

 

[Dave Ramage]

Add this to the relevant sheet code module:

Private Sub CommandButton_Search_Click()
'''Search this worksheet for text in TextBox_Search
'''If found, then activate cell, if not found, then display message
Dim r As Excel.Range

Set r = Me.Cells.Find(What:=Me.TextBox_Search, LookIn:=xlValues)
If r Is Nothing Then
MsgBox prompt:="Data not found!"
Else
r.Activate
End If
End Sub

Change the control names as required.

Cheers,
Dave