Monday, February 28, 2022

VBA - User Forms: Search as you Type using TextBox and ListBox | Search ...

at

3 comments:

  1. AnonymousJuly 25, 2022 at 3:40 AM

    Dear Hay, you share with me this code ? or ready excell ?

    ReplyDelete
  2. AnonymousDecember 23, 2022 at 1:44 AM

    HI Hay, ou share with me this code ? or ready excell ?

    ReplyDelete
  3. AnonymousDecember 23, 2022 at 1:44 AM

    Dim criterian

    Private Sub ComboBox1_Change()
    Dim c As Integer
    Dim column_headers
    column_headers = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")
    For c = 1 To 9
    If Arkusz1.Cells(1, c).Value = Me.ComboBox1.Value Then
    criterion = column_headers(c - 1)
    End If
    Next
    Arkusz1.Cells(1, "K").Value = criterion

    Me.ListBox1.Clear
    Me.TextBox1.Value = ""
    Me.TextBox1.SetFocus

    End Sub


    Private Sub TextBox1_Change()
    On Error Resume Next

    If Me.TextBox1 = "" Then
    Me.ListBox1.Clear
    Exit Sub
    End If

    Me.ListBox1.Clear
    Dim r, last_row As Integer
    last_row = Arkusz1.Range("A10000").End(xlUp).Row
    For r = 2 To last_row
    a = Len(Me.TextBox1.Text)
    If UCase(Left(Arkusz1.Cells(r, criterion).Value, a)) = UCase(Me.TextBox1.Text) Then
    With Me.ListBox1

    .AddItem Arkusz1.Cells(r, "A").Value
    .List(.ListCount - 1, 1) = Arkusz1.Cells(r, "B").Value
    .List(.ListCount - 1, 2) = Arkusz1.Cells(r, "C").Value
    .List(.ListCount - 1, 3) = Arkusz1.Cells(r, "D").Value
    .List(.ListCount - 1, 4) = Arkusz1.Cells(r, "E").Value
    .List(.ListCount - 1, 5) = Arkusz1.Cells(r, "F").Value
    .List(.ListCount - 1, 6) = Arkusz1.Cells(r, "G").Value
    .List(.ListCount - 1, 7) = Arkusz1.Cells(r, "H").Value
    .List(.ListCount - 1, 8) = Arkusz1.Cells(r, "I").Value
    End With
    End If
    Next r
    End Sub

    Private Sub UserForm_Initialize()
    Dim c As Integer
    For c = 1 To 9
    Me.ComboBox1.AddItem Arkusz1.Cells(1, c).Value
    Next

    With Me.ListBox1
    .ColumnCount = 9
    .ColumnWidths = "80;80;80;80;80;80;80;80;80"

    End With

    End Sub


    WHAT I Wrong ?

    ReplyDelete

Subscribe to: Post Comments (Atom)