Option Explicit Private Sub TextBox1_Change() Dim rng As Range Dim r As Range Dim strFilt As String Dim strFiltLen As Integer Dim arrFilt As Variant 'динамический массив Dim ColCnt As Integer Dim i As Integer, c As Integer ColCnt = ActiveSheet.UsedRange.Columns.Count Set rng = ActiveSheet.UsedRange strFilt = TextBox1.Text strFiltLen = Len(strFilt) i = 0 ReDim arrFilt(ColCnt - 1, i) For Each r In rng.Rows If strFilt = Left(r.Cells(1.1).Text, strFiltLen) Then ReDim Preserve arrFilt(ColCnt - 1, i) For c = 1 To ColCnt arrFilt(c - 1, i) = r.Cells(1, c).Value Next i = i + 1 End If Next ListBox1.Column() = arrFilt End Sub Private Sub TextBox1_Enter() TextBox1.BackColor = RGB(255, 255, 0) End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox1.BackColor = RGB(255, 255, 255) End Sub Private Sub UserForm_Initialize() Dim ColCnt As Integer Dim rng As Range Dim cw As String Dim c As Integer ColCnt = ActiveSheet.UsedRange.Columns.Count Set rng = ActiveSheet.UsedRange With ListBox1 .ColumnCount = ColCnt '.RowSource = rng.Address cw = "" For c = 1 To .ColumnCount cw = cw & rng.Columns(c).Width & ";" Next c .ColumnWidths = cw '.ListIndex = 0 End With TextBox1_Change End Sub |