Excel Forum

Code: Select all

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

    For i = 2 To lastRow
        If Worksheets("Bget").Cells(i, 11).Value = ProductId Then
            Worksheets("Bget").Cells(i, 3).Value = TextBox2.Text
            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, 10).Value = TextBox7.Text
        End If
    Next
'    ActiveSheet.Protect Password:="1"
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox1.SetFocus
End Sub

Code: Select all

Worksheets("Bget").Cells(i, 3).Value = TextBox2.Value

Code: Select all

Worksheets("Bget").Cells(i, 3).Value = Format(CDate(TextBox2.Value), "d mmm yyyy")

Code: Select all

 CDate(TextBox2.Value)

Code: Select all

Private Sub CommandButton1_Click()
    Dim ProductId As String
    ProductId = TextBox1.Text
    lastRow = Worksheets("Bget").Cells(Rows.Count, 11).End(xlUp).Row
    
    If ProductId = "" Then
        MsgBox "กรุณากรอกรหัสอ้างอิง"
        TextBox1.SetFocus
    End If

    For i = 2 To lastRow
        If Worksheets("Bget").Cells(i, 11).Value = ProductId Then
            TextBox2.Text = Worksheets("Bget").Cells(i, 3).Value
            TextBox3.Text = Worksheets("Bget").Cells(i, 4).Value
            TextBox4.Text = Worksheets("Bget").Cells(i, 5).Value
            TextBox5.Text = Worksheets("Bget").Cells(i, 7).Value
            TextBox6.Text = Worksheets("Bget").Cells(i, 8).Value
            TextBox7.Text = Worksheets("Bget").Cells(i, 10).Value
        End If
        Next
        If Worksheets("Bget").Cells(i, 11).Value <> ProductId Then
            MsgBox "ไม่มีรหัสอ้างอิง"
            TextBox1.SetFocus
        End If
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim ProductId As String
    ProductId = TextBox1.Text
    lastRow = Worksheets("Bget").Cells(Rows.Count, 11).End(xlUp).Row
    If ProductId = "" Then
        MsgBox "กรุณากรอกรหัสอ้างอิง"
        TextBox1.SetFocus
    End If
    x = Application.Match(ProductId, Worksheets("Bget").Cells(2, 11).Resize(lastRow).Value, 0)
     If Not IsNumeric(x) Then
            MsgBox "ไม่มีรหัสอ้างอิง"
           TextBox1.SetFocus
    End If
    For i = 2 To lastRow
        If Worksheets("Bget").Cells(i, 11).Value = ProductId Then
            TextBox2.Text = Worksheets("Bget").Cells(i, 3).Value
            TextBox3.Text = Worksheets("Bget").Cells(i, 4).Value
            TextBox4.Text = Worksheets("Bget").Cells(i, 5).Value
            TextBox5.Text = Worksheets("Bget").Cells(i, 7).Value
            TextBox6.Text = Worksheets("Bget").Cells(i, 8).Value
            TextBox7.Text = Worksheets("Bget").Cells(i, 10).Value
        End If
        Next
End Sub