September 10, 2017

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:

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

  1. 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.

  2. 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?

  3. 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

  4. 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.

  5. Hola, no hablo inglés pero me gustaría que me ayudaras a pasar datos de un libro de Excel a otro que esté cerrado de acuerdo a una condición

    1. Will
      Of the command buttons are working great except for the search button. I’m getting a run-time error Object required this is the line where it stops working.
      If Trim(Sheet1.Cells(i, 1)) Trim(cboPARCS.Text) And i = totRows Then.

  6. Dear sir
    Good day to you, I need to make user form , can you get it made in class as project work. And you charge me for the same.

    Regards
    Ashutosh

  7. Hi
    I want to know about the searching a data of a workbook in a sheet but in my workbook each sheet have about 20 to 30 tables of different items for example
    SHEET 1 NAME IS ENGLISH BOOKS
    ALL English writers book detail I have in this sheet.
    Cell A1 to H1 is merged for the name of item example = Lesson Books.
    A2 = date of the book purchase
    B2= name of book
    C2= pages of book
    D2= name of the book writer.
    E2=price of book
    F2= Number of the same book Purchase
    G2= shelf address
    H2 publisher name
    A2, B2, C2, D2, E2, F2, G2, H2
    A3, B3, C3, D3, E3, F3, G3, H3
    There are many tables like this so what can i do for search my in different sheets.

  8. Dim i As Long, GetRow As Long
    For i = 3 To Sheets(“Sheet1”).Cells(Rows.Count, 1).End(xlUp).Row
    ‘Criteria search
    If Sheets(“Sheet1”).Cells(i, 1).Value = LstTerritory.Value And Trim(Sheets(“sheet1”).Cells(i, opt2)) = “True” Then

    ‘Returns row
    GetRow = i
    MsgBox (” Row ” & GetRow)
    End If
    Next i

    here first value to be checked is in first column and its input would be derived from list box technonoly

    also in that same if coloum no. ( opt2) would come from different function whose value can be True or False.
    now i need to get all rows which has technology = it and column opt2 value = true.

  9. Hi its not works when I modify the sheet name (example “emp_details”), even though I changed it inside source code also. Error shown as

    Run-time error ‘424’:
    Object required

  10. Hello Sir, really loe to see your video. I have some problem creating next and previous button. could you help me finishing my project

    Private Sub cmdAdd_Click()
    Dim x As Long
    Dim y As Worksheet
    Set y = Sheets(“sheet1”)
    x = y.Range(“B” & Rows.Count).End(xlUp).Row

    With y
    .Cells(x + 1, “B”).Value = txtEmployee_Name.Text
    .Cells(x + 1, “C”).Value = txtFather_Name.Text
    .Cells(x + 1, “D”).Value = txtMother_Name.Text
    .Cells(x + 1, “E”).Value = txtContact.Text
    .Cells(x + 1, “F”).Value = txtAddress.Text
    .Cells(x + 1, “G”).Value = txtDob.Text
    .Cells(x + 1, “H”).Value = txtQualification.Text
    .Cells(x + 1, “I”).Value = txtGender.Text
    .Cells(x + 1, “J”).Value = txtDesignation.Text
    .Cells(x + 1, “K”).Value = txtBlood_Group.Text
    .Cells(x + 1, “L”).Value = txtMarital_Status.Text
    .Cells(x + 1, “M”).Value = txtBank.Text
    .Cells(x + 1, “N”).Value = txtAccount_Number.Text
    .Cells(x + 1, “O”).Value = txtName_Account.Text
    .Cells(x + 1, “P”).Value = txtCode.Text
    End With

    MsgBox “Data Save”, vbOKOnly + vbInformation, “Data Base”

    txtEmployee_Name.Text = “”
    txtFather_Name.Text = “”
    txtMother_Name.Text = “”
    txtContact.Text = “”
    txtAddress.Text = “”
    txtDob.Text = “”
    txtQualification.Text = “”
    txtGender.Text = “”
    txtDesignation.Text = “”
    txtBlood_Group.Text = “”
    txtMarital_Status.Text = “”
    txtBank.Text = “”
    txtAccount_Number.Text = “”
    txtName_Account.Text = “”
    txtCode.Text = “”

    Me.txtEmployee_Name.SetFocus

    End Sub

    Private Sub cmdQuit_Click()
    Unload Me
    End Sub

    Private Sub cmdReset_Click()
    Unload Me
    UserForm1.Show
    End Sub

    Private Sub cmdSearch_Click()

    Dim x As Long
    Dim y As Long
    x = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

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

    For y = 2 To x
    If Sheets(“sheet1”).Cells(y, 2).Text = txtsearch.Value Then

    txtEmployee_Name.Text = Sheets(“sheet1”).Cells(y, 2)
    txtFather_Name.Text = Sheets(“sheet1”).Cells(y, 3)
    txtMother_Name.Text = Sheets(“sheet1”).Cells(y, 4)
    txtContact.Text = Sheets(“sheet1”).Cells(y, 5)
    txtAddress.Text = Sheets(“sheet1”).Cells(y, 6)
    txtDob.Text = Sheets(“sheet1”).Cells(y, 7)
    txtQualification.Text = Sheets(“sheet1”).Cells(y, 8)
    txtGender.Text = Sheets(“sheet1”).Cells(y, 9)
    txtDesignation.Text = Sheets(“sheet1”).Cells(y, 10)
    txtBlood_Group.Text = Sheets(“sheet1”).Cells(y, 11)
    txtMarital_Status.Text = Sheets(“sheet1”).Cells(y, 12)
    txtBank.Text = Sheets(“sheet1”).Cells(y, 13)
    txtAccount_Number.Text = Sheets(“sheet1”).Cells(y, 14)
    txtName_Account.Text = Sheets(“sheet1”).Cells(y, 15)
    txtCode.Text = Sheets(“sheet1”).Cells(y, 16)
    Exit For
    End If
    Next y
    End Sub

    Private Sub cmdUpdate_Click()
    Dim x As Long
    Dim y As Long
    x = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
    For y = 2 To x
    If Sheets(“sheet1”).Cells(y, 2).Text = txtsearch.Value Then
    Sheets(“sheet1”).Cells(y, 2) = txtEmployee_Name.Text
    Sheets(“sheet1”).Cells(y, 3) = txtFather_Name.Text
    Sheets(“sheet1”).Cells(y, 4) = txtMother_Name.Text
    Sheets(“sheet1”).Cells(y, 5) = txtContact.Text
    Sheets(“sheet1”).Cells(y, 6) = txtAddress.Text
    Sheets(“sheet1”).Cells(y, 7) = txtDob.Text
    Sheets(“sheet1”).Cells(y, 8) = txtQualification.Text
    Sheets(“sheet1”).Cells(y, 9) = txtGender.Text
    Sheets(“sheet1”).Cells(y, 10) = txtDesignation.Text
    Sheets(“sheet1”).Cells(y, 11) = txtBlood_Group.Text
    Sheets(“sheet1”).Cells(y, 12) = txtMarital_Status.Text
    Sheets(“sheet1”).Cells(y, 13) = txtBank.Text
    Sheets(“sheet1”).Cells(y, 14) = txtAccount_Number.Text
    Sheets(“sheet1”).Cells(y, 15) = txtName_Account.Text
    Sheets(“sheet1”).Cells(y, 16) = txtCode.Text
    Exit For
    End If
    Next y
    MsgBox “Data Base Updated”, vbOKOnly + vbInformation, “Personal Data”
    End Sub

Comments are closed.