Option Explicit
Dim x

Private Sub UserForm_Initialize() 'Задаём диапазон значений для Комбобокса
Dim i&

With Sheets("Лист2")
    If .FilterMode Then .ShowAllData
    x = .Range("A1:G" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With

'With CreateObject("Scripting.Dictionary") 'как вариант
'    .CompareMode = 1
'    For i = 2 To UBound(x)
'        If Not .Exists(x(i, 4)) Then .Item(x(i, 4)) = Empty    'Значение из БД
'    Next i
'    Me.ComboBox1.List = .keys
'End With

With CreateObject("System.Collections.ArrayList")
    For i = 2 To UBound(x)
        If Not .Contains(x(i, 4)) Then .Add x(i, 4)
    Next i
    .Sort
    Me.ComboBox1.List = .toArray
End With
End Sub

Private Sub ComboBox1_Change()
If Me.ComboBox1.ListIndex = -1 Then Exit Sub
Dim s$, i&, k&
s = Me.ComboBox1.Value
Call ClearTextBox

On Error Resume Next: Err.Clear
For i = 2 To UBound(x)
    If x(i, 4) = s Then
        k = k + 1
        Me("TextBox" & k).Text = x(i, 1)
        If Err Then MsgBox "Not found TextBox " & k, 48
    End If
Next i
On Error GoTo 0
End Sub

Private Sub ClearTextBox()
Dim i&
For i = 1 To 6
    Me("TextBox" & i).Text = vbNullString
Next i
End Sub