Option Explicit 
Option Compare Text 
Dim bu As Boolean 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
If Target.CountLarge > 1 Then Exit Sub 
If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub 
If Target.Column = 2 Then 
bu = True 
With Me.TextBox1 
.Top = Target.Top: .Text = Target.Value ': .Activate 
End With 
With Me.ListBox1 
.Top = Target.Top + 5 
If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ 
(ActiveWindow.Application.Height + ActiveWindow.Application.Top) Then _ 
.Top = .Top - .Height + Target.Height '* ActiveWindow.Zoom / 100 
.Clear 
End With 
bu = False 
Me.TextBox1.Visible = True: Me.ListBox1.Visible = True 
Else 
Me.TextBox1.Visible = False: Me.ListBox1.Visible = False 
End If 
End Sub 

Private Sub TextBox1_Change() 
If Len(TextBox1.Text) = 0 Or bu Then Exit Sub 'при отсутствии символов для поиска - выход 
Dim x, i As Long, txt As String, lt As Long, s As String 
txt = TextBox1.Text: lt = Len(TextBox1.Text) 
x = Columns(32).SpecialCells(2).Offset(1).Value 
For i = 1 To UBound(x, 1) ' поиск по первым буквам 
If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" 
Next i 
ListBox1.List = Split(s, "~") 
End Sub 

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
If KeyCode = 13 Or KeyCode = 9 Then 
With Me.TextBox1 
ActiveCell.Value = .Value 
.Visible = False: ListBox1.Visible = False 
End With 
ActiveCell(2, 1).Select 
End If 
End Sub 

Private Sub ListBox1_Click() 
If ListBox1.ListIndex = -1 Then Exit Sub 
Application.EnableEvents = False 
bu = True 
With Me.ListBox1 
ActiveCell.Value = .Value 
Me.TextBox1.Text = .Value 
Me.TextBox1.Visible = False: .Visible = False 
End With 

Application.EnableEvents = True 
bu = False 
End Sub 

'Sub tt() 
'Application.EnableEvents = True 
'End Sub