Listbox population using two codes supplied

[ipbr21054]

Afternoon,
Could you give me some assistance please.

Supplied below are two codes.

My job is to use the last code for populating the listbox as opposed to the code in the first supplied code.
Once its been loaded i wish the HONDA SHEET to then be active so when a name is selected in the listbox it will then look for its match in column C

Many thanks if you can advise.

Private Sub TextBox1_Change()
 Dim r As Range, f As Range, Cell As String, added As Boolean
 Dim sh As Worksheet
 
 Set sh = Sheets("HONDA SHEET")
 sh.Select
 With ListBox1
 .Clear
 .ColumnCount = 2
 .ColumnWidths = "100;0"
 If TextBox1.Value = "" Then Exit Sub
 Set r = Range("C21", Range("C" & Rows.Count).End(xlUp))
 Set f = r.Find(TextBox1.Value, LookIn:=xlValues, LookAt:=xlPart)
 If Not f Is Nothing Then
 Cell = f.Address
 Do
 added = False
 For i = 0 To .ListCount - 1
 Select Case StrComp(.List(i), f.Value, vbTextCompare)
 Case 0, 1
 .AddItem f.Value, i
 .List(i, 1) = f.Row
 added = True
 Exit For
 End Select
 Next
 If added = False Then
  .AddItem f.Value
 .List(.ListCount - 1, 1) = f.Row
 End If
 Set f = r.FindNext(f)
 Loop While Not f Is Nothing And f.Address <> Cell
 TextBox1 = UCase(TextBox1)
 Else
 MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "DATABASE SHEET CUSTOMER NAME SEARCH"
 TextBox1.Value = ""
 TextBox1.SetFocus
 End If
 End With
End Sub

Private Sub NEWSHEET_Click()
Application.ScreenUpdating = False
Dim wsH As Worksheet: Set wsH = Sheets("HONDA SHEET")
Dim wsS As Worksheet
Sheets.Add(After:=Sheets("INFO")).Name = "SORT SHEET"
Set wsS = Sheets("SORT SHEET")
wsH.Range("C21", wsH.Range("C" & wsH.Rows.Count).End(xlUp)).Copy wsS.Range("A1")
wsS.Range("A1", wsS.Range("A" & wsS.Rows.Count).End(xlUp)).Sort wsS.[A1], 1
Application.ScreenUpdating = True
End Sub

 

[ipbr21054]

Now sorted thanks.