Search for Data In Excel Database with UserForm

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: