Snasui Excel Forum

Code: Select all

Option Explicit 
Private Sub TextBox6_Change() 
    Dim x, i As Long, ii As Long, iii As Integer, e As Long 
    x = [_Data] 
    With ListBox1 
        If TextBox6.Value = vbNullString Then 
            .List = x 
        Else 
            e = IIf(OptionButton1, 1, 4) 
            For i = 1 To UBound(x, 1) 
                If LCase(x(i, e)) Like LCase(TextBox6.Value) & "*" Then 
                    .AddItem 
                    For ii = 1 To 9 
                        .List(.ListCount - 1, ii - 1) = x(i, ii) 
                    Next 
                End If 
            Next 
        End If 
    End With 
End Sub 

Code: Select all

Private Sub TextBox6_Change()
   Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 1)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub

Code: Select all

Private Sub TextBox6_Change()
   Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 4)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub

Code: Select all

Private Sub TextBox6_Change()
   Dim x, i As Long, ii As Long, iii As Integer
    x = [_Data]
    With ListBox1
        If TextBox6 = "" Then
            .RowSource = "_Data"
        Else
            .RowSource = ""
            For i = 1 To UBound(x, 1)
                If LCase(x(i, 1)) Like LCase(TextBox6) & "*" Then
                    For ii = 1 To 9
                        .AddItem
                        .List(iii, ii - 1) = x(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
End Sub

Code: Select all

Private Sub CommandButton4_Click() ' edit
Dim sonsat As Long
If ListBox1.ListIndex = -1 Then
        MsgBox "คุณยังไม่ได้ใส่ข้อมูล", vbInformation, "ระบบค้นหาสำนวน"
        Me.TextBox6.SetFocus
Exit Sub
End If
    lastrow = Sheets("Database").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Database").Range("A2:I" & lastrow).Find(What:=ListBox1.Text, LookIn:=xlValues, LookAt:=xlWhole).Activate
    sonsat = ActiveCell.Row
        Cells(sonsat, 1) = Me.TextBox1.Value
        Cells(sonsat, 2) = Me.TextBox2.Value
        Cells(sonsat, 3) = Me.TextBox3.Value
        Cells(sonsat, 4) = Me.TextBox4.Value
        Cells(sonsat, 5) = Me.ComboBox3.Value
        Cells(sonsat, 6) = Me.ComboBox4.Value
        Cells(sonsat, 7) = Me.ComboBox1.Value
        Cells(sonsat, 8) = Me.ComboBox2.Value
        Cells(sonsat, 9) = Me.TextBox5.Value
        Me.TextBox6.SetFocus
        MsgBox "แก้ไขข้อมูลเรียบร้อย", vbInformation, "ระบบค้นหาสำนวน"
        ListBox1.List = Sheets("Database").Range("A2:I" & Sheets("Database").Cells(Rows.Count, 1).End(xlUp).Row).Value
        Me.TextBox7.Value = ListBox1.ListCount
        ActiveWorkbook.Save
End Sub

Code: Select all

Private Sub TextBox6_Change()
    Dim i As Long
    Dim n As Long
    Dim Str As String
    Str = Me.TextBox6.Text
    n = Me.ListBox1.ListCount
    For i = 0 To n - 1
        If Me.ListBox1.List(i) = Str Then
            Me.ListBox1.ListIndex = i
            Exit Sub
        End If
    Next i
End Sub

Code: Select all

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
    Dim n As Long
    Dim Str As String
    Str = Me.TextBox6.Text
    n = Me.ListBox1.ListCount
    For i = 0 To n - 1
        If Left(Me.ListBox1.List(i), Len(Str)) > 1 Then
            Me.ListBox1.ListIndex = i
            Exit Sub
        End If
    Next i
End Sub

Code: Select all

If Me.ListBox1.Selected(i) = True Or TextBox1.Text = "" Then

Code: Select all

Private Sub TextBox6_Change()
    Dim i As Long
    Dim n As Long
    Dim Str As String
    Str = Me.TextBox6.Text
    n = Me.ListBox1.ListCount
    For i = 0 To n - 1
        If Left(Me.ListBox1.List(i), Len(Str)) = Str Then
            Me.ListBox1.ListIndex = i
            Exit Sub
        End If
    Next i
End Sub