How Do I Create a Data Entry Form With the Find Button in Excel?

Search for data in your Excel spreadsheet.

You can easily create a data entry form with a find button in your Microsoft Excel application by using a “UserForm” and Visual Basic for Applications (VBA). A “UserForm” is a user interface where you can place controls such as text boxes and buttons. VBA is the programming language you can use to execute the “Find” method command and search for text in your Excel worksheet. The “Find” method will search for a term and returns the value if it finds it; otherwise it returns nothing.

Instructions

1. Launch Microsoft Excel, type “one” in A1, “two” in A2, “three” in A3, “four” in A4, and “five” in A5. Click the “Developer” tab and click “Visual Basic.” Click the “Insert” menu and clicking “UserForm” to add a new form. Click “TextBox” on the Toolbox pane and click the form to add a new text box. Add a new “Label” using the same technique. Add a “CommandButton” to your form also using the same technique.

2. Double-click the button to create a click event for the button. Add the following code to define a “GoTo” statement to capture errors:

   Private Sub CommandButton1_Click()
   On Error GoTo Err_CommandButton1_Click:

3. Define the string variable you will use to save the text you want to find in your worksheet:

   Dim findStr As String
   findStr = TextBox1.Text

4. Copy and paste the following code to execute the “Find” method and search the worksheet for the value entered in the text box control:

   Me.Label1.Caption = Cells.Find(What:=findStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
   :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
   False).Value & " was found in your worksheet!"

5. Add the following code to end the click event and display a message if the string was not found:

   Exit_CommandButton1_Click:
   Exit Sub
   Err_CommandButton1_Click:
   MsgBox ("The string you entered was not found in your worksheet!")
   Resume Exit_CommandButton1_Click:
   End Sub

6. Press “F5” to run the program and type “two” in the text box control. Press “CommandButton1” to display a message on the label or a message box if the text was not found.

References

• MSDN: Range.Find Method (Excel)