Botón Buscar datos para Formularios VBA en Excel 🔎 (Múltiple Criterio)

Introduction

In this tutorial, you will learn how to create a search button for a VBA form in Excel that allows users to search for data using multiple criteria. This functionality is particularly useful for managing large datasets, making it easier to find specific information quickly. By following these steps, you will be able to enhance your Excel forms and improve user interaction.

Step 1: Create the Form

• Open Excel and navigate to the Developer tab.
• Click on "Insert" and choose "UserForm" to create a new form.
• Use the toolbox to add necessary controls such as text boxes, labels, and buttons to your form.

Step 2: Call the Search Form

• In the VBA editor, create a new subroutine to display the search form.
• Use the following code to show the form:

  Sub ShowSearchForm()
      UserForm1.Show
  End Sub
  

Step 3: Assign Macro to the Search Button

• On your UserForm, select the search button you created.
• In the properties window, set the OnClick event to call the search macro:

  Private Sub btnSearch_Click()
      Call SearchData
  End Sub
  

Step 4: Populate the Form Data

• Use the Initialize event of the UserForm to fill the form with initial data.
• Example code:

  Private Sub UserForm_Initialize()
      ' Populate initial data
  End Sub
  

Step 5: Add Headers to the Form

• If your form includes a list or table, ensure you add headers.
• You can do this by setting the caption property for each column header.

Step 6: Select Specific Column for Search

• Create a dropdown or combo box for users to specify which column to search.
• Populate this control with the names of the columns in your dataset.

Step 7: Loop Through Rows

• Write a loop to go through each row in your dataset.
• Example code snippet:

  For i = 2 To LastRow
      ' Check conditions
  Next i
  

Step 8: Add Items to the Results

• As you find matches, add them to a list box or another control to display the results.
• Use code like:

  ListBox1.AddItem FoundValue
  

Step 9: Implement Data Search

• Write a function to check if input values match the criteria.
• This function should be case-insensitive, allowing for flexible searches:

  Function MatchCriteria(value As String, criteria As String) As Boolean
      MatchCriteria = (UCase(value) Like UCase(criteria))
  End Function
  

Step 10: Allow Partial Name Searches

• Modify your search criteria to allow users to input partial names instead of full names.
• This can be done using the Like operator in your search function.

Step 11: Search by Last Name

• If you want to allow searching by last name, ensure the corresponding logic is implemented in your search loop.
• Example:

  If MatchCriteria(LastNameColumn, txtLastName.Text) Then
      ' Add to results
  End If
  

Step 12: Use Double Click to Return Personal Data

• Set up a double-click event on the results list to display personal data in the form.
• Example code:

  Private Sub ListBox1_DblClick()
      ' Display selected data
  End Sub
  

Conclusion

By following these steps, you have created a functional search button for a VBA form in Excel that allows users to search using multiple criteria. This feature can greatly improve the usability of your Excel applications, making data retrieval faster and more efficient. Consider expanding your application further by adding more features or refining the user interface. Happy coding!