T4121_Display Search Results In ListBox Capture Row In Sheet

https://excel-egy.com/forum/t4121
----------------------------------

Dim ws As Worksheet
Const colCount As Integer = 21

Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Worksheets("Sheet1")
End Sub

Private Sub TextBox1_Change()
    Dim a, ary(), txt As String, r As Long, rr As Long, c As Long, cc As Long

    With ws
        a = .Range("A2:T" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Value
        txt = Me.TextBox1
        Me.ListBox1.Clear

        For r = 1 To UBound(a, 1)
            If InStr(1, .Cells(r + 1, "A"), txt, vbTextCompare) = 1 Then
                rr = rr + 1
                ReDim Preserve ary(1 To colCount, 1 To rr)
                For c = 1 To colCount
                    cc = Choose(c, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21)
                    If cc = 21 Then
                        ary(c, rr) = .Cells(r + 1, cc).Row
                    Else
                        ary(c, rr) = .Cells(r + 1, cc).Value
                    End If
                Next c
            End If
        Next r
    End With

    If rr Then Me.ListBox1.Column = ary
    Erase ary

    If TextBox1.Text = "" Then ListBox1.Clear
End Sub

Private Sub ListBox1_Click()
    Dim myRow As Long

    ws.Cells.Interior.ColorIndex = 0

    If Me.ListBox1.ListIndex <> -1 Then
        Me.Top = 300
        myRow = Me.ListBox1.List(Me.ListBox1.ListIndex, 20)
        ws.Rows(myRow).EntireRow.Interior.ColorIndex = 6
        Application.Goto ws.Range("A" & myRow), True
    End If
End Sub

Private Sub CommandButton1_Click()
    ws.Cells.Interior.ColorIndex = 0
    Unload Me
End Sub