How to code a 'Search Button'

[mssianlf]

Hi,
I was looking for some help in coding a search button.

I need a single text box in which people can type a name and press go. I then need the data displayed in a user-form. already have the user form as it is the same format as the one I have created for data entry and the data returned needs to be editable. The data is held in one workbook and I only need two columns to be searchable.
I know there is a lookup option but tbh the people I have been charged with creating this for have trouble with ctrl+f .

I am sure this is a complicated and time consuming ask and will be grateful for any help anyone can offer as it took me nearly a whole week to make my user form work

Thank you in advance.

Sian

 

[Richard Schollar]

This is a possible kind of routine you could run:

Sub Show_Lines()
Dim ws As Worksheet
Dim strFirstAddr As String
Dim rngFoundCell As Range
Dim rngAllFoundCells As Range
Dim vSoughtForTerm As Variant
Set ws = ActiveSheet
vSoughtForTerm = Application.InputBox("Please enter text to search for", "Text Search", Type:=2)
If Len(vSoughtForTerm) = 0 Or vSoughtForTerm = False Then Exit Sub
With ws.UsedRange.Offset(1)  'offset by 1 to preserve first row (expecting to contain headers)
    .EntireRow.Hidden = False
    Set rngFoundCell = .Find(What:=vSoughtForTerm, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
    If Not rngFoundCell Is Nothing Then
        strFirstAddr = rngFoundCell.Address
        Set rngAllFoundCells = rngFoundCell
        Do
            Set rngFoundCell = .FindNext(rngFoundCell)
            If rngFoundCell.Address <> strFirstAddr Then
                Set rngAllFoundCells = Union(rngAllFoundCells, rngFoundCell)
            End If
        Loop Until rngFoundCell.Address = strFirstAddr
        .Rows.Hidden = True
        For Each rngFoundCell In rngAllFoundCells
            rngFoundCell.EntireRow.Hidden = False
        Next rngFoundCell
    Else
        MsgBox "No cells found with sought for term!"
    End If
End With
End Sub

It uses an input box to query what term you want to search on and then hides all rows after the first row that don't contain the sought for term.

 

[mssianlf]

Hi

That's fab except it doesn't return any results even though I know the result is lurking there. Do I have to tell it to search a particular sheet as I have just been asked to add another sheet at the front for statistics. This is where the button will reside.

 

[mssianlf]

Also -Can I get the results to un hide with another button?

 

[Richard Schollar]

Re unhiding with another button - sure. You could use something like this:

For Each ws In Worksheets
  ws.UsedRange.EntireRow.Hidden = False
Next ws

As to the first point - try this modified code:

Sub Show_Lines()
Dim ws As Worksheet
Dim strFirstAddr As String
Dim rngFoundCell As Range
Dim rngAllFoundCells As Range
Dim vSoughtForTerm As Variant
 
Application.CommandBars("Workbook Tabs").ShowPopup

Set ws = ActiveSheet
vSoughtForTerm = Application.InputBox("Please enter text to search for", "Text Search", Type:=2)
If Len(vSoughtForTerm) = 0 Or vSoughtForTerm = False Then Exit Sub
With ws.UsedRange.Offset(1)  'offset by 1 to preserve first row (expecting to contain headers)
    .EntireRow.Hidden = False
    Set rngFoundCell = .Find(What:=vSoughtForTerm, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
    If Not rngFoundCell Is Nothing Then
        strFirstAddr = rngFoundCell.Address
        Set rngAllFoundCells = rngFoundCell
        Do
            Set rngFoundCell = .FindNext(rngFoundCell)
            If rngFoundCell.Address <> strFirstAddr Then
                Set rngAllFoundCells = Union(rngAllFoundCells, rngFoundCell)
            End If
        Loop Until rngFoundCell.Address = strFirstAddr
        .Rows.Hidden = True
        For Each rngFoundCell In rngAllFoundCells
            rngFoundCell.EntireRow.Hidden = False
        Next rngFoundCell
    Else
        MsgBox "No cells found with sought for term!"
    End If
End With
End Sub

 

[BarefootPaul]

I addressed something like this recently (with help from folks on here) with a combobox search. You might be able to adapt the code to meet your specific needs. I had a button on my form to search for a record that brought up a different form with a combobox with 4 columns (one for each of the info that I needed). I then set the bound column data into the original form with:

frmOriginal.txtRecordID.Value = cboLookup.Value

Then I had setup the VBA to fill other text boxes on the original form when the txtRecordID changed:

Private Sub txtRecordID_Change()
Dim vLook As Range
Dim vFName As String
Dim vLNname As String
Dim vDOB As Date
Dim vMaine As String
vMaine = txtRecordID.Value
If vMaine = vbNullString Then
    Exit Sub
    Else
    With ActiveWorkbook.Sheets("Clients")
        Set vLook = .Range("A:D")
        vFName = Application.VLookup(vMaine, vLook, 2, False)
            If IsError(vFName) Then
                MsgBox ("Error is with vFName")
            Exit Sub
            Else
        vLName = Application.VLookup(vMaine, vLook, 3, False)
            If IsError(vLName) Then
                MsgBox ("Error is with vLName")
            Exit Sub
            Else
        vDOB = Application.VLookup(vMaine, vLook, 4, False)
            If IsError(vDOB) Then
                MsgBox ("Error is with vDOB")
            Exit Sub
            End If
            End If
            End If
    End With
End If
txtFName.Value = vFName
txtLName.Value = vLName
txtDOB.Value = vDOB
txtDOA.SetFocus
End Sub

You can then change any of the text boxes, as long as you don't change the txtRecordID box.

 

[mssianlf]

That's fab. All works perfectly.

Thanks