How to search for data in Excel database with UserForm. We have learnt how to navigate through our database, clear the controls on the user-form, add data to our database while avoiding duplicates and how to update records.

watch the video below to learn how to search for data in the Excel worksheet with a search button on a user-form:

Watch this video on YouTube.

Here’s the VBA code:

Dim currentrow As Long ‘declare the global variable

Private Sub UserForm_Initialize()
currentrow = 2
TextBox1 = Cells(currentrow, 1)
TextBox2 = Cells(currentrow, 2)
TextBox3 = Cells(currentrow, 3)

End Sub

Private Sub cmdSearch_Click()
Dim totRows As Long, i As Long

totRows = Worksheets(“Sheet1”).Range(“A1”).CurrentRegion.Rows.count

If TextBox1.Text = “” Then
MsgBox “Enter the name you wish to search!”
End If

For i = 2 To totRows
If Trim(Sheet1.Cells(i, 1)) <> Trim(TextBox1.Text) And i = totRows Then
MsgBox “Name not found!”
End If
If Trim(Sheet1.Cells(i, 1)) = Trim(TextBox1.Text) Then
TextBox1.Text = Sheet1.Cells(i, 1)
TextBox2.Text = Sheet1.Cells(i, 2)
TextBox3.Text = Sheet1.Cells(i, 3)
currentrow=i ‘capture the value of the currentrow
Exit For
End If
Next i

End Sub

Further reading:

Create User-Form with Navigation Buttons

How to code Clear and Add Command Buttons on User-Form

Add data to Excel Database with User-Form using the COUNTIF function

Download a sample file for practice: