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)
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

 

 

4 thoughts on “Search for Data In Excel Database with UserForm

  1. Zeca Ferreira

    I am developing an excel user form to manage a simple database. My problem is creating a search button. I want to create a search button that can search through all records in my worksheet using multiple criteria and return all matches.
    For example. in my data base I keep the records of all customers and events I have participated.

    For example, when I press my search button I want all the Customer and the Events information to appear in my user form.
    I want to be able to enter Customer Number and Event Date in the textBox1 and textBox2.

    would you be able to provide me with some insight and example code how would I achieve this?
    Thanks for your help in advanced.

    Reply
  2. vanheer

    Thank you for this great article.
    I noticed that after search is conducted, the “currentrow” variable is not changed to match the searched input. so after you search for a record, and if you made changes and want to update record or want to delete the record, the form updates or deletes the “currentrow” used. is there a way to select the row where the search found?

    Reply
  3. Hans van Houwelingen

    Hello,

    I first want to thank you for your great helping videos.
    But I have a problem with your video “Search for Data In Excel Database with Userform”.
    https://www.youtube.com/watch?v=02HsNqdpM2k

    I’m not working with a Userform but with the cells on a sheet called “Search_Addresses”.

    I don’t know how to solve this problem.
    I have a sheet called “DATA” with all the data from my friends.
    On another sheet called “Search_Addresses”, I have some cells to search for a specific friend.

    “Search_Addresses” cell E8 searches in “DATA” column A for a combination from Place-Street and Number, by data validation.
    When the data is found all the data from my friend are shown in “Search_Addresses” cells E16, E18, E20 and so on, by data validation.

    If it’s necessary it’s my wish to complete or change the data by typing other data in the cells I just called and then enter the button.

    But the VBA is constantely having a problem with the yellow collorred sentence !
    My question is : Can you please help me to solve this problem ?

    Greetings from the Netherlands
    Hans

    Private Sub Sheet_Search_Adresses_Initialize()
    currentrow = 1
    Range(“E8”) = Cells(currentrow, 1)
    Range(“E16”) = Cells(currentrow, 2)
    Range(“E18”) = Cells(currentrow, 3)
    Range(“E20”) = Cells(currentrow, 3)

    End Sub

    Sub Button6_Click()

    Dim totRow As Long, i As Long
    If Range(“E8”).Text = “” Then
    MsgBox “First place an adress”
    End If
    totRows = Worksheets(“DATA”).Range(“A1”).CurrentRegion.Rows.Count
    For i = 2 To totRows
    If Trim(E8.Cells(i, 1)) Trim(E8.Text) And i = totRows Then
    MsgBox “Adress is not found !”
    End If

    If Trim(E8.Cells(i, 1)) Trim(E8.Text) And i = totRows Then
    MsgBox “Adress not found !”
    End If

    If Trim(Data.Cells(i, 1)) = Trim(E11.Text) Then
    Range(“E16”).Text = Data.Cells(i, 5)
    Range(“E18”).Text = Data.Cells(i, 7)
    Range(“E20”).Text = Data.Cells(i, 8)

    Exit For
    End If
    Next i
    End Sub

    Reply
  4. Sadiq Ishaq

    Hi Sir Dinesh Kumar Takyar,
    First i want to say thank you for your great helpfulness videos.
    Dear, i’m making a userform helping from your videos. In userform i have some issue. I have a column of “Gender” and i’m using option button for this. In “Add Data” command button its working true but in “Search” command button option button not working.
    Please tell me code for search data in userform from database using optionbutton.
    thanks.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *