Snasui Excel Forum

Code: Select all

Private Sub TextBox1_Change()
Dim i As Range, rAll As Range
    If TextBox1.Text <> "" Then
        ListBox1.Clear
        With Sheets("hit91")
            Set rAll = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        End With
        For Each i In rAll
            A = Len(TextBox1.Text)
            If Left(i.Value, A) = Left(TextBox1.Text, A) Then
              ListBox1.AddItem i.Value

            End If
        Next i
    End If
End Sub

Code: Select all

Dim r As Range, rAll As Range, j As Integer
If TextBox1.Text <> "" Then
    ListBox1.Clear
    With Sheets("hit91")
        Set rAll = .Range("A2:I55", .Range("A" & .Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        A = Len(TextBox1.Text)
        If Left(r.Value, A) = Left(TextBox1.Text, A) Then
            With ListBox1
                .ColumnWidths = "50 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
                .AddItem
                .List(j, 0) = r.Value
                .List(j, 1) = r.Offset(0, 1).Value
                .List(j, 2) = r.Offset(0, 2).Value
                '...Other code
                j = j + 1
            End With
        End If
    Next r
End If

Code: Select all

Private Sub CommandButton1_Click()
    Dim username As String
    Dim i As Integer
    Dim irow As Long
    Dim ws As Worksheet
    username = TextBox1.Text
    If username = "1234" Then
        MsgBox "ยินดีต้อนรับเข้าสู่ระบบคุณภาพ", vbInformation
        Unload Me 'unload this
        UserForm2.Show
    Else
        i = i + 1
        If i < 3 Then
            MsgBox "Username  ไม่ถูกต้อง", vbCritical
            'now close the workbook
        Else
            Application.DisplayAlerts = False
            Application.Quit
        End If
    End If
    Set ws = Worksheets("username")
    'find first empty row in username
    irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
    'copy the data to the username
    
    ws.Cells(irow, 1).Value = Date
    ws.Cells(irow, 2).Value = Time
    ws.Cells(irow, 3).Value = Me.TextBox1.Value
End Sub