Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
    Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Data")
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 2) _
        .End(xlUp).Offset(1, 0).Row
    'Check for Id
    If Trim(Me.TextBox1.Value) = "" Then
        Me.TextBox1.SetFocus
        MsgBox "ãÊèª×èÍÊÔ¹¤éÒ"
        Exit Sub
    End If
    'copy the data to the database
    ws.Cells(irow, 1).Value = Me.TextBox1.Value
    ws.Cells(irow, 2).Value = Me.ComboBox1.Value
    ws.Cells(irow, 3).Value = Me.TextBox2.Value
    ws.Cells(irow, 4).Value = Me.ComboBox2.Value
    ws.Cells(irow, 5).Value = Me.TextBox3.Value
    ws.Cells(irow + 1, 1).Value = Me.TextBox1.Value
    ws.Cells(irow + 1, 2).Value = Me.ComboBox1.Value
    ws.Cells(irow + 1, 3).Value = Me.TextBox4.Value
    ws.Cells(irow + 1, 4).Value = Me.ComboBox3.Value
    ws.Cells(irow + 1, 5).Value = Me.TextBox5.Value
    
    'Clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.ComboBox1.Value = ""
    Me.ComboBox2.Value = ""
    Me.ComboBox3.Value = ""
    Me.TextBox1.SetFocus
    If CommandButton1 Then
        UserForm1.Hide
    End If
End Sub

Code: Select all

Private Sub ComboBox1_Change()
   TextBox1.Value = Application.VLookup(Me.ComboBox1, Sheets("Supplier").Range("B:D"), 2, False)
   TextBox2.Value = Application.VLookup(Me.ComboBox1, Sheets("Supplier").Range("B:D"), 3, False)
End Sub