Excel Forum

Code: Select all

Private Sub CommandButton2_Click()
    Dim ProductId As String
    ProductId = ComboBox1.Text
'    ProductId = TextBox1.Text
    lastRow = Worksheets("Bget").Cells(Rows.Count, 11).End(xlUp).Row
            If ComboBox1.value = "" Then
'            If TextBox1.Value = "" Then
                MsgBox "กรุณากรอกรหัสอ้างอิงก่อนการอัพเดตข้อมูล"
                    TextBox1.value = ""
                    TextBox2.value = ""
                    TextBox3.value = ""
                    TextBox4.value = ""
                    TextBox5.value = ""
                    TextBox6.value = ""
                    TextBox7.value = ""
                    TextBox1.SetFocus
                    TextBox1.BackColor = vbGreen
                Exit Sub
            End If

    For i = 2 To lastRow
        If Worksheets("Bget").Cells(i, 11).value = ProductId Then
            Worksheets("Bget").Cells(i, 3).value = CDate(TextBox2.value)
            Worksheets("Bget").Cells(i, 4).value = TextBox3.Text
            Worksheets("Bget").Cells(i, 5).value = TextBox4.Text
            Worksheets("Bget").Cells(i, 7).value = TextBox5.Text
            Worksheets("Bget").Cells(i, 8).value = TextBox6.Text
            Worksheets("Bget").Cells(i, 9).value = TextBox7.Text
        End If
    Next
    TextBox1.value = ""
    TextBox2.value = ""
    TextBox3.value = ""
    TextBox4.value = ""
    TextBox5.value = ""
    TextBox6.value = ""
    TextBox7.value = ""
    TextBox1.SetFocus
    Unload Me
End Sub

Code: Select all

'Other code
For i = 2 To lastRow
    If Worksheets("Bget").Cells(i, 11).Value = ProductId Then
        If IsNumeric(Worksheets("Bget").Cells(i, 3).Value2) Then
            If Worksheets("Bget").Cells(i, 3).Value2 < 200000 Then
                TextBox2.Text = Worksheets("Bget").Cells(i, 3).Value
            Else
                TextBox2.Text = ""
                MsgBox "Date is incorrrect format.", vbExclamation
            Exit Sub
            End If
        Else
            TextBox2.Text = ""
            MsgBox "Date is incorrrect format.", vbExclamation
        End If
        TextBox3.Text = Worksheets("Bget").Cells(i, 4).Value
'Other code