Snasui Excel Forum

Code: Select all

Private Sub Save_Click()
Dim i As String
    On Error Resume Next
If TextBox1.Text = "TB1" Then
i = WorksheetFunction.VLookup((TB1), Sheets("Yummy").Range("E5:FK1939"), 1, False)
Worksheets("Yummy").Cells(i, 155) = TextBox2.Text
Worksheets("Yummy").Cells(i, 156) = TextBox3.Value
Worksheets("Yummy").Cells(i, 157) = TextBox4.Value
Worksheets("Yummy").Cells(i, 160) = TextBox5.Value
Worksheets("Yummy").Cells(i, 163) = TextBox6.Value
Worksheets("Yummy").Cells(i, 165) = TextBox7.Value
Worksheets("Yummy").Cells(i, 158) = TextBox8.Value
Worksheets("Yummy").Cells(i, 159) = TextBox9.Value
Worksheets("Yummy").Cells(i, 162) = TextBox10.Value
Worksheets("Yummy").Cells(i, 164) = TextBox11.Value
Worksheets("Yummy").Cells(i, 166) = TextBox12.Value
Worksheets("Yummy").Cells(i, 167) = TextBox13.Text
Worksheets("Yummy").Cells(i, 161) = TextBox14.Value
Unload Me
    'Clear the data
TextBox2.Text = " "
TextBox3.Value = " "
TextBox4.Value = " "
TextBox5.Value = " "
TextBox6.Value = " "
TextBox7.Value = " "
TextBox8.Value = " "
TextBox9.Value = " "
TextBox10.Value = " "
TextBox11.Value = " "
TextBox12.Value = " "
TextBox13.Text = " "
TextBox14.Value = " "
MsgBox "บันทึกข้อมูลสำเร็จ"
End If
End Sub

Code: Select all

Private Sub Save_Click()
Dim i As String
    On Error Resume Next
    'If TextBox1.Text = "TB1" Then
    'i = WorksheetFunction.VLookup((TB1), Sheets("Yummy").Range("E5:FK1939"), 1, False)
    j = Application.CountIf(Sheets("Yummy").Range("e4:e1939"), Me.TextBox1.Text)
    If j > 0 Then
        i = Application.Match(Me.TextBox1.Text, Sheets("Yummy").Range("e1:e1939"), 0)
    Else
        MsgBox "Not found data. Please try again.", vbInformation
        Exit Sub
    End If
    
    Worksheets("Yummy").Cells(i, 155) = TextBox2.Text
    Worksheets("Yummy").Cells(i, 156) = TextBox3.Value
    Worksheets("Yummy").Cells(i, 157) = TextBox4.Value
    Worksheets("Yummy").Cells(i, 160) = TextBox5.Value
    Worksheets("Yummy").Cells(i, 163) = TextBox6.Value
    Worksheets("Yummy").Cells(i, 165) = TextBox7.Value
    Worksheets("Yummy").Cells(i, 158) = TextBox8.Value
    Worksheets("Yummy").Cells(i, 159) = TextBox9.Value
    Worksheets("Yummy").Cells(i, 162) = TextBox10.Value
    Worksheets("Yummy").Cells(i, 164) = TextBox11.Value
    Worksheets("Yummy").Cells(i, 166) = TextBox12.Value
    Worksheets("Yummy").Cells(i, 167) = TextBox13.Text
    Worksheets("Yummy").Cells(i, 161) = TextBox14.Value
    Unload Me
    'Clear the data
    TextBox2.Text = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Text = ""
    TextBox14.Value = ""
    MsgBox "บันทึกข้อมูลสำเร็จ"
    'End If
End Sub