Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
   Dim irow, orow As Long
    Dim ws, os As Worksheet
    Dim ow As Workbook
    Set ws = Worksheets("Database")
    Set ow = Workbooks("Combobox1.xlsm")
    Set os = ow.Worksheets("Data")
    'find first empty row in database
    irow = ws.Cells(Rows.Count, 1) _
        .End(xlUp).Offset(1, 0).Row
       orow = os.Cells(Rows.Count, 1) _
        .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.TextBox2.Value
    ws.Cells(irow, 3).Value = Me.TextBox4.Value
    ws.Cells(irow, 4).Value = Me.ComboBox1.Value
    ws.Cells(irow, 5).Value = Me.TextBox5.Value
    ws.Cells(irow, 6).Value = Me.TextBox6.Value
    ws.Cells(irow, 7).Value = Me.TextBox11.Value
    ow.Activate
    os.Cells(orow, 1).Value = Me.TextBox1.Value
    os.Cells(orow, 2).Value = Me.TextBox2.Value
    os.Cells(orow, 3).Value = Me.TextBox4.Value
    os.Cells(orow, 4).Value = Me.ComboBox1.Value
    os.Cells(orow, 5).Value = Me.TextBox5.Value
    os.Cells(orow, 6).Value = Me.TextBox6.Value
    os.Cells(orow, 7).Value = Me.TextBox11.Value
    'ws.Cells(irow, 8).Value = Me.TextBox8.Value
    'ws.Cells(irow, 9).Value = Me.TextBox9.Value
    'ws.Cells(irow, 10).Value = Me.TextBox11.Value
    'ws.Cells(irow + 1, 1).Value = Me.TextBox1.Value
    'ws.Cells(irow + 1, 2).Value = Me.TextBox2.Value
    'ws.Cells(irow + 1, 3).Value = Me.TextBox4.Value
    'ws.Cells(irow + 1, 4).Value = Me.ComboBox1.Value
    'ws.Cells(irow + 1, 5).Value = Me.TextBox5.Value
    'ws.Cells(irow + 1, 6).Value = Me.TextBox6.Value
    'ws.Cells(irow + 1, 7).Value = Me.TextBox7.Value
    'ws.Cells(irow + 1, 8).Value = Me.TextBox8.Value
    'ws.Cells(irow + 1, 9).Value = Me.TextBox9.Value
    'ws.Cells(irow + 1, 10).Value = Me.TextBox10.Value
    
    'Clear the data
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
    Me.TextBox6.Value = ""
    Me.TextBox11.Value = ""
    'Me.TextBox8.Value = ""
    'Me.TextBox9.Value = ""
    'Me.TextBox10.Value = ""
    'Me.TextBox11.Value = ""
    'Me.TextBox1.SetFocus
    If CommandButton1 Then
        UserForm1.Hide
    End If

End Sub